How to distinguish trigger actions in sql?

Question

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 

Answer 1

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

Answer 2

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
;