際際滷

際際滷Share a Scribd company logo

SQL Triggers

Aaron Buma
Aaron Buma

The slides and scripts for this training are available at: http://www.aaronbuma.com/2016/01/SQLTriggers/ Thanks for watching! Check out the other SQL Training videos on my channel.

1 of 12
Downloaded 22 times
Triggers Presentation by Aaron Buma Dev Lead and DBA
Session Overview The AFTER triggers Instead Of triggers Nesting DDL Triggers Demos
The AFTER Triggers A stored procedure that runs after an event occurs, but before initial event is committed Event Types: AFTER INSERT, AFTER DELETE and AFTER UPDATE Only one of each event type allowed They can be disabled or enabled
The AFTER Triggers Will fire only if at least one row is affected Logic in the trigger can rollback the insert or run other logic A copy of the data can be referenced by special tables: Inserted or Deleted Synonymous with a FOR trigger
AFTER DELETE Using TRUNCATE to clear a table ignores triggers The deleted row is moved from the table into the Deleted special table
AFTER UPDATE Before and After data are stored in the special tables: Deleted Inserted
Instead Of Triggers Similar to After triggers, but prevents direct action against table (with Insert, Update or Delete) You can: Access Inserted and Deleted special tables Rollback the Insert, Update or Delete
Nesting Triggers A trigger on one table will activate a trigger on another @@NESTLEVEL: Level 1 = In the trigger of the initial action Level 2 = In the trigger of an action generated in Level 1
DDL Triggers Triggers at the Database Level AFTER CREATE_TABLE AFTER DROP_TABLE Only AFTER, or FOR allowed Special Tables are not available (Inserted, Deleted)
DDL Triggers and EVENTDATA() EVENTDATA() Shows Event details, XML Datatype Can be searched in a DDL Trigger to look for non-conforming (coding standards) values
DEMOS
Review Presentation Recording, 際際滷s and Scripts http://www.AaronBuma.com/2016/01/SQLTriggers/ Contact: Aaron@EmergencyReporting.com

More Related Content

SQL Triggers

  • 1. Triggers Presentation by Aaron Buma Dev Lead and DBA
  • 2. Session Overview The AFTER triggers Instead Of triggers Nesting DDL Triggers Demos
  • 3. The AFTER Triggers A stored procedure that runs after an event occurs, but before initial event is committed Event Types: AFTER INSERT, AFTER DELETE and AFTER UPDATE Only one of each event type allowed They can be disabled or enabled
  • 4. The AFTER Triggers Will fire only if at least one row is affected Logic in the trigger can rollback the insert or run other logic A copy of the data can be referenced by special tables: Inserted or Deleted Synonymous with a FOR trigger
  • 5. AFTER DELETE Using TRUNCATE to clear a table ignores triggers The deleted row is moved from the table into the Deleted special table
  • 6. AFTER UPDATE Before and After data are stored in the special tables: Deleted Inserted
  • 7. Instead Of Triggers Similar to After triggers, but prevents direct action against table (with Insert, Update or Delete) You can: Access Inserted and Deleted special tables Rollback the Insert, Update or Delete
  • 8. Nesting Triggers A trigger on one table will activate a trigger on another @@NESTLEVEL: Level 1 = In the trigger of the initial action Level 2 = In the trigger of an action generated in Level 1
  • 9. DDL Triggers Triggers at the Database Level AFTER CREATE_TABLE AFTER DROP_TABLE Only AFTER, or FOR allowed Special Tables are not available (Inserted, Deleted)
  • 10. DDL Triggers and EVENTDATA() EVENTDATA() Shows Event details, XML Datatype Can be searched in a DDL Trigger to look for non-conforming (coding standards) values
  • 11. DEMOS
  • 12. Review Presentation Recording, 際際滷s and Scripts http://www.AaronBuma.com/2016/01/SQLTriggers/ Contact: Aaron@EmergencyReporting.com