Tuesday, March 13, 2018

SQL SERVER - INSERT trigger

Sometimes you need to perform some actions immediately after you insert row to table. For this purpose you can use insert trigger on the table.
Pseudo-table named "inserted" contains inserted data row - you probably utilize it here.
ALTER trigger [dbo].[sql_insert]
on [dbo].[sql]
for insert
as
  begin transaction;
  
  begin try

    /* -- add new initial role */

    insert into [role_object]
    ( ident, [description], [role_object_type_ident] )
    select inserted.id, null, 'Q' from inserted;  
  
    /* -- add log row about creation to log table */
  
    insert into sql_detail_changes
    ( sql_id, [description], [type] )
    select inserted.id, 'Creation', 'I' from inserted;  
    
    if @@TRANCOUNT > 0 commit transaction;      
  end 

  /* -- problem ? */

  try begin catch
    if @@TRANCOUNT > 0 rollback transaction;      
  end catch;  

No comments:

Post a Comment