Today, I want to talk about triggers in MsSqlServer. Triggers are type of stored procedure that can make INSERT, UPDATE or DELETE operations on a table. The triggers differ from the stored procedures in that the triggers are fired automatically when a predefined data modification occurs.
There are two types of DML triggers: AFTER or FOR trigger and INSTEAD OF trigger. The AFTER trigger will be fired and executed after performing the INSERT, UPDATE or DELETE action that fires it successfully. Also, any referential cascade actions and constraint checks should succeed before firing the trigger.
We can use triggers for many purposes. For example, you have a student table and you want to save deleted records to another table. So you write a trigger fires when a record deleted from student table.
And you will insert deleted data to another table with the trigger.
Let’s make a trigger now. We must give the affected table name and type of operation (AFTER DELETE) At the same time, we must write the executed sql in the begin – end block.
|
1 2 3 4 5 6 |
CREATE TRIGGER AfterDeleteStudentTrigger ON Student AFTER DELETE AS INSERT INTO DeletedStudent VALUES ((SELECT TOP 1 deleted.ID FROM deleted), 'Delete') GO |
We can enable or disable our triggers when it is needed by this code.
|
1 |
enable trigger trigger_name On table_name |
We can create a trigger in order to prevent deleting record from a table.
|
1 2 3 4 5 6 7 |
Create Trigger trg_TypeNotDeleting on type after delete as begin raiserror('You cannot delete a record from type ',1,1) rollback transaction end |
If you are searching for Stored Procedures, have a look at this post: Stored Procedures In MsSql At the same time, you can look at my other post about Jobs In MsSql
If you have question do not forget to write from the chat button next to it or from the comment.