0

I'd like to create on Trigger that is fired on insert, update and delete. Inside the trigger I need to handle those actions in different ways. How can I find out which action triggered the trigger ? Or, should I use multiple triggers for different actions ?

'if update(FIRSTNAME)' works fine, but how to differentiate insert and delete? 'if insert(FIRSTNAME)' obviously does not work.

ALTER TRIGGER "FR_CHANGES" AFTER INSERT, DELETE, UPDATE
ORDER 1 ON "DBA"."PERSONAL"
referencing old as old_rec new as new_rec
FOR EACH ROW
BEGIN
    if update(FIRSTNAME) or update(LASTNAME) then
       insert into FR_CHANGES (PNR_PERSONAL, CHANGE_TYPE) values (old_rec.PNR, "update")  
    end if
    if insert(FIRSTNAME) then //this line gives an error
       insert into FR_CHANGES (PNR_PERSONAL, CHANGE_TYPE) values (new_rec.PNR, "insert")  
    endif
END
user1119859
  • 669
  • 2
  • 9
  • 20

2 Answers2

0

You can distinguish by the existance of the row in the virtutal table insert/delete, like that:


IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)
BEGIN

print 'actions after insert statement'


END
ELSE IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)
BEGIN


print 'actions after update statement'

END

ELSE IF EXISTS (select 1 from deleted) AND NOT EXISTS (select 1 from inserted)
BEGIN

print 'actions after delete statement'

END
Wojtek
  • 11
  • 4
  • When changing it this way, I get a "syntax error" in line 8. This is the second "begin" command in the trigger. ALTER TRIGGER "FR_CHANGES" AFTER INSERT, DELETE, UPDATE ORDER 1 ON "DBA"."PERSONAL" referencing old as old_rec new as new_rec FOR EACH ROW BEGIN IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) BEGIN print 'actions after insert statement' END END – user1119859 Mar 24 '23 at 11:39
  • Isn't it ELSEIF in Sybase? – Wojtek Mar 24 '23 at 11:57
  • If I do select * from inserted, I get the message that there is no such table – user1119859 Mar 24 '23 at 12:07
  • These tables will only recognized within trigger definition. – Wojtek Mar 24 '23 at 12:37
  • unfortunatly the same error (table inserted does not exist) when executed inside a Trigger. – user1119859 Mar 24 '23 at 13:09
0

You could discriminate between an insert & delete event by smth like this, perhaps

alter trigger trg_test_table 
on test_table for insert, delete 
as 
begin
  print 'inside trigger'
if (select count(*)     from deleted) > 0 
  begin 
    print 'test_table delete'
  end
if (select count(*)     from inserted) > 0 
  begin 
    print 'test_table insert'
  end
end
;
access_granted
  • 1,807
  • 20
  • 25