An insert trigger is a Data Manipulation Language (DML) trigger that acts when a new record is added to the table.
CREATE TRIGGER t_tableName_insert
ON tableName
AFTER|FOR|INSTEAD OF INSERT
AS
Some bit of T-SQ
OK, let’s walk through this. CREATE TRIGGER begins the command. The next part t_tableName_insert is the trigger’s name. I follow a convention of t for trigger, underscore, then the table name, another underscore, and finally the trigger type (in this case insert). We then have to identify the table this trigger is for with ON tableName. Next we have two paths we can follow.
We can create an AFTER INSERT or INSTEAD OF INSERT.
- AFTER INSERT — Actually is the same thing as FOR INSERT. The T-SQL code will be run after all the operations in the triggering statement (and any cascade actions) have completed.
- INSTEAD OF INSERT — replaces the functionality of the INSERT statement with whatever T-SQL code you include.
It’s important to understand when the trigger runs. Some people think the trigger will run after each record is inserted. That’s not the case, you have to stop thinking about the record, and think about the set. That simple change (simple to describe, not so simple to actually change your thoughts), can take you from someone learning SQL to someone who is mastering SQL.
While the trigger is executing you have access to a new temporary table called inserted. This table has a copy of all the records that were created during the current batch. This let’s you do things like auditing or custom logging. Rather than having to do some weird join to figure out which records were inserted, you can just get them from this table!
Triggers are greatly misunderstood by those who aren’t trying to master SQL. They are especially misunderstood by those who try to do databases as part of their job, rather than the whole of it. Like every tool in SQL, there is a time and a place for it. You have to listen to all the requirements (business and programming) before implementing your tool. Performance, security, and reliability are all to be considered before you choose how to implement.
And remember to load test your solution before moving to production!
That’s all for today. If you have any questions about triggers, send them in. I’m here to help you learn everything you need for Microsoft SQL Server!