How To Create A Trigger In MSSql Server
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.
merhabalar, içeriğiniz gerçekten çok verimli.Yazılarınızın devamını bekliyorum.Kolay gelsin
Thank you for every other great article. Where
else may just anybody get that kind of info in such an ideal way of writing?
If some one wishes expert view about blogging and site-building afterward i prokpose him/her to pay a visit this
web site, Keep up the fastidious work.
Wow, this paragraph is good, my younger sister is analyzing such things,
so I am going to convey her. http://rgcr.info/
I am extremely inspired together with your writing skills as well as with the
format for your blog. Is that this a paid topic or did you customize it
your self? Anyway stay up the excellent quality writing, it is uncommon to look a nice blog like this one nowadays..
Hi, I do believe this is a great site. I stumbledupon it 😉 I’m going to revisit once again since
I book-marked it. Money and freedom is the greatest way to change, may you be rich and continue to guide other people.
I am regular reader, how are you everybody? This paragraph posted at this web page is really pleasant.
Excellent blog here! Additionally your site lots up very fast!
What host are you the usage of? Can I get your associate hyperlink for
your host? I desire my website loaded up as fast as yours lol
It’s hard to find well-informed people about this subject, but you sound like you know what you’re talking about!
Thanks
I really like your blog.. very nice colors & theme.
Did you design this website yourself or did you hire
someone to do it for you? Plz reply as I’m looking to construct my own blog and would
like to know where u got this from. many thanks