Hi, In this article I am going to explain all about triggers in MS SQL.
A trigger is a kind of stored procedure that automatically executes when any event occurs in the database server. Triggers are mainly classified as two types. one is DML triggers and the other is DDL triggers.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event like INSERT, UPDATE, or DELETE statements on a table or view.
A DML trigger can contain normal sql statements or stored procedures. The trigger and the statement that fires it are treated as a single transaction. We can rolled back the transaction from within the trigger, If a severe error is found the entire transaction will automatically rolled back.
DML triggers again classified into 3 types. They are 1.After Triggers 2. Instead of Triggers.
After triggers are fired after any DML action done on any of the table which the trigger has been created.
Instead of triggers are fired when any DML action takes place on any of the table or view which the trigger has been created.
After triggers fired after the DML action done on the object
Instead of triggers are fired at the same time when any DML action taking place on the object.
Syntax for cteating a trigger on a table for INSERT, UPDATE, DELETE.
CREATE TRIGGER [trg_triggerName] ON [dbo].[table_Name] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
--Here we can write your sql statements or we can execute your stored procedures.
END
we can write a trigger for each DML action separately. like one trigger for INSERT, one for UPDATE and one for DELETE. No need to write all the DML actions in one trigger. We can define the triggers as per our criteria.
But make sure that your trigger could not include a select query.
Of course for fetching data on a simple table is ok but querying from groupi of tables or on a table having huge data will make the database performance slow.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|