April 11, 2006 at 4:15 am
I have created trigger which checks if data is changed it updates note column of that row
Create trigger tr_test on mytable after UPDATE
AS
declare @strNote as varchar(200)
set @strNote=''
..........
if update(StatusId2) and (Select StatusId2 from deleted) <>(Select StatusId2 from inserted)
set @strNote=@strNote + ' Old StatusId2: ' + (Select StatusId2 from deleted)
....
It works fine if I update one row at a time But Fails when I execute a update statement that affects more that one row. Coz subquery returns more than one row.
U'r advise will be appriciated
Vikas
April 11, 2006 at 5:13 am
Vikas,
Try this:
if update(StatusId2)
update mytable
set = ' Old StatusId2: ' + d.StatusId2
from mytable mt
join deleted d on d.PrimaryKeyField = mt.PrimaryKeyField -- change to correct column
and d.StatusId2 mt.StatusId2
Something along those lines anyway. It's difficult to be more precise because I cannot tell what the structure of your table is like.
Hope that helps,
April 11, 2006 at 5:30 am
Thanx Karl
It will work, But Is there any option to make trigger run for each row even I execute batch update
e.g
Update mytable set statuscode='ACT' where statuscode is null
If above query will affect 10 rows then in Trigger I can see 10 rows in Inserted and Delete table Which requred cursor to handle each row
Is ther other alternative to cursor
April 11, 2006 at 7:29 am
The solution I provided you with will update every row that is affected by your update statement and removes the need to use a cursor.
A trigger will only run for each update/insert/delete statement that is executed - regardless of how many rows they affect.
Unless you have some other logic in your trigger that makes you think you need to run it for every row I cannot see that you need anything else.
The whole point of SQL is that it is set based so you shouldn't need a cursor to handle each row.
Hope that helps,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply