Member-only story
#40 Advanced T-SQL (part 3): Trigger
Trigger is created to automatically conduct 1 or several actions to any significant event (own preference) that has been made to the database. It is usually used to check constraints of different tables or several records of a table. Every table has 3 commands to make changes: UPDATE, DELETE or INSERT. We can pull a trigger while/after or not performing these changes as FOR/AFTER/INSTEAD OF respectively in the syntax of creating trigger as follows.
In SQL Server, there are 3 types of triggers:
- DML: for instance whenever create a new table
- DDL: for instance update, delete or insert new values to a table
- Login: for instance log-in, log-out event
Magic tables
Whenever a DML trigger is executed, 2 magic tables named “deleted” and “inserted” are automatically created as follows. The structure of these 2 tables are as the same as the base table. Now, recall that in DML events we have either UPDATE or DELETE or INSERT. With DELETE and INSERT actions, these two magic tables function as follows:
- Inserted: this table records whatever insertion to the base table.
- Deleted: all rows that have been deleted will be seen in this table.
With UPDATE, both these tables are in action.
