April 6, 2004 at 9:56 am
I'm new to writing triggers. The following simple trigger works fine. However, is there anything I should do differently. It's a trigger that says...
when prod_psz.prim_stor_zone us UPDATEd,if the new value is '555' set prod_master.last_pick ='Y'. If the new value <> '555' set prod_master.last_pick ='N'.
For instance, instead of the two IF/BEGIN/END blocks, I could just have a single UDPATE join that uses SET field = CASE WHEN...THEN...END. What about error handling? Not sure if it matters. Any suggestions would be appreciated. Thanks.
/*************************************************/
CREATE TRIGGER tr_Test
ON prod_psz FOR update
AS IF UPDATE(PRIM_STOR_ZONE)
Begin
Declare @Old_Psz varchar(3)
Declare @New_Psz varchar(3)
Declare @Product varchar(32)
Select @PRODUCT = prod_id from inserted
Select @Old_Psz = prim_stor_zone from deleted
Select @New_Psz = prim_stor_zone from inserted
If @New_Psz = '555' and @Old_Psz<>'555'
Begin
Update prod_master
set last_pick = 'Y'
where prod_id = @PRODUCT
End
If @Old_Psz = '555' and @New_Psz<>'555'
Begin
Update prod_master
set last_pick = 'N'
where prod_id = @PRODUCT
End
END
smv929
April 6, 2004 at 12:21 pm
I don't think it matters if you do it with the two if's or use the case function in an UPDATE. It will matter when @Old_Psz = '555' AND @New_Psz = '555'. In the trigger you posted no update will run. If you usa an UPDATE statement with the case function then an update will run even when the two values are the same unless you have an if statement to ensure it doesn't run in that case.
Another thing to note about this trigger is that if more than one record is updated in the same transaction only one will be checked by the IF's, probably the last one.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
April 6, 2004 at 12:27 pm
smv929,
Your trigger seems to be expecting that only one row has changed. In some situations you can rule out the possibility of getting more than one row changed at a time.
Generally, however, you should always avoid triggers that depends on only one row being changed at a time.
If somebody changes multiple rows with a single sql statement, your trigger will only be called once, and it is better to be prepared for that, for example by doing all of your updating with a single update statement that handles all the cases at once, joining the inserted and deleted pseudotables where appropriate.
Also, if you're new to triggers, you might be surprised that the UPDATE(PRIM_STOR_ZONE)
actually evaluates to true even when the PRIM_STOR_ZONE isn't actually modified. The only condition that it checks, is whether PRIM_STOR_ZONE is referred in the update stmt.
HTH
Mads
April 7, 2004 at 5:30 am
This will take care of single-row or multi-row updates :
create trigger tr_test
on prod_psz for update
as if update(prim_stor_zone)
begin
update
b
set
b.last_pick = 'Y'
from
inserted as a
join prod_master as b on b.prod_id = a.prod_id
join deleted as c on c.prod_id = b.prod_id
where
a.prim_stor_zone = '555' and
c.prim_stor_zone '555'
update
b
set
b.last_pick = 'N'
from
inserted as a
join prod_master as b on b.prod_id = a.prod_id
join deleted as c on c.prod_id = b.prod_id
where
c.prim_stor_zone = '555' and
a.prim_stor_zone '555'
end
April 7, 2004 at 9:19 am
Thanks to all for pointing out these things, which was exactly what I needed. It was indeed updating only the current row, which would have been ok in my scenario because only one product (record) is updated at a time by our application. However, I modified it to handle multiple records updated simulataneously just in case someone manually updates several records. In any case, I learned an important lesson about triggers.
Thanks especially to roberthamilton. Your code worked perfectly!
smv929
April 7, 2004 at 10:08 am
By the way, I had to modify the trigger slightly to handle INSERTs as well as UPDATEs. The JOINs worked fine for updates because both the inserted and deleted tables contained records. However on inserts, the inserted table contained a record but the deleted table doesn't. So I added a LEFT join and modified the WHERE clause to include "(c.prim_stor_zone = '555' or c.prim_stor_zone is null)". This works, but if anyone sees a newbie mistake let me know. Thanks again!
--------------------------------------------
CREATE
TRIGGER tr_UpdateLastPickFlag
ON PROD_PSZ
FOR INSERT, UPDATE
AS IF UPDATE(PRIM_STOR_ZONE)
set nocount on
begin
--Set last_pick=Y WHERE the new (a) PSZ is 555 and the old is not
update b
set b.last_pick = 'Y'
from inserted a
join prod_master as b on b.prod_id = a.prod_id
left join deleted as c on c.prod_id = b.prod_id
where
a.prim_stor_zone = '555'
and (c.prim_stor_zone <> '555' or c.prim_stor_zone is null)
--Set last_pick=N WHERE the old (c) PSZ was 555 but is no longer
update b
set b.last_pick = 'N'
from inserted a
join prod_master b on b.prod_id = a.prod_id
left join deleted c on c.prod_id = b.prod_id
where
(c.prim_stor_zone = '555' or c.prim_stor_zone is null) and
a.prim_stor_zone <> '555'
end
smv929
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply