The types of triggers in SQL Server include DML, DDL and logon triggers. A customer requested this three-part summary.
| Kind | Event and scope |
|---|---|
| DML | INSERT, UPDATE or DELETE on a table or view. AFTER and INSTEAD OF have distinct behavior. |
| DDL | Supported schema or definition events, such as CREATE, ALTER and DROP. Scope can be database or server. |
| Logon | A LOGON event after authentication succeeds, before the user session is established. Server scope. |
DML triggers process statements and inserted/deleted rowsets that can contain multiple rows. Avoid single-row assumptions. Logon triggers execute after authentication rather than for failed authentication. An incorrect design can block normal connections.
I prefer explicit business workflows instead of unnecessary triggers. That does not mean every trigger is wrong. Auditing, controlled DDL and other requirements deserve their own design assessment.
Inspect event, scope, enabled state and dependencies before changes. The videos provide related examples. This conceptual explanation does not create or disable a trigger.
Reference: Trigger kinds and event behavior.
Related reading
- Comprehensive Database Performance Health Check
- SQL in Sixty Seconds series
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
A trigger category is not the complete design, it is an event scope whose behavior and dependencies still matter.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
Thank yoiu
DML events include INSERT, UPDATE, or DELETE statements…
If I had the power to decide, I’d avoid triggers as well. But as most departments just buy software and make me implement it, the use of triggers is the choice of the unknown software developers of those products.
I only have 70 triggers using cursors in the current production server I am working on. So with business logic. 🤐
Tough.