SQL:Target a Trigger

I remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver, if used cleverly. Many of the users might know Triggers so I will use question-answer format to clear the doubts about Trigger which keeps pounding now and then.

What is a Trigger?

A trigger is nothing more than a stored procedure that runs automatically under certain conditions.

What Are These Conditions?

Triggers are tightly coupled to its tables and can only fire if any modification happens to these tables.

What are These Modifications?

These modifications can be insertion, deletion or updation of data. For example: A query like any of these can fire a trigger.

INSERT INTO employee (id, name, age) VALUES (1, 'CodeSpread', 27);
DELETE from employee where age= 27; UPDATE employee SET age=30 where id=1

How It Can Be Created?

Triggers are tightly coupled to a table so we write it like:

CREATE TRIGGER employee_delete
ON employee FOR DELETE
AS PRINT GETDATE()
go

This example creates an employee_delete trigger on table employee which gets fired when an employee gets deleted from the table. In response, this trigger prints the date.

How does it Look Like?

We can see that FolderSeq is a trigger attached to Table folder.

What are the Types of Triggers?

Types of triggers can be classified based on their origin of events like:

• Data Manipulation Language (DML) Triggers: It fires only when DML commands like INSERT, DELETE, and UPDATE executes. [Data operation]
• Data Definition Language (DDL) Triggers: It fires when changes to database objects happen like CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS executes. [Object Operation]

Now, we will talk about DML triggers. There are two types of triggers under this category.

• After Trigger: AFTER triggers fire after the data modification statement completes but before the statement’s work is committed to the databases.
• INSTEAD OF trigger: INSTEAD OF triggers are a bit different as they do not follow the full logic of triggers. They work before the desired operation of a trigger. They can be used to perform error or value checking on one or more columns and then perform additional actions before inserting, updating or deleting the row or rows. But this insert, update or delete operation never actually happens, instead of this, operation defined under instead of condition happens.

Note: Instead of triggers carries a benefit of running on views also.

We are avoiding code here to keep the article short as a lot of theory is still pending. We might include a tutorial on this topic in the near future. Now, we move to DDL triggers. There are two types of DDL triggers.

1. Transact-SQL DDL Trigger

A special type of Transact-SQL stored procedure that executes one more Transact-SQL statements in response to a server-scoped or database-scoped event. For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

2. CLR DDL Trigger

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

ref: http://msdn.microsoft.com/en-us/library/ms175941.aspx

Why or When to Use Triggers?

Triggers is more likely a business requirement than a technical justification. We can broadly classify the requirements which require a trigger to be implemented, though the list of conditions can NOT be treated as a final list.

• Auditing: Can be treated as where any modifications to the coupled table happens, then the data like ‘modified date’ and ‘modified by’ are maintained for auditing purposes, so we can compare before and after data values resulting from inserts, updates and deletions. [How can we compare before and after data values?]
• Validation and Security: Field Level Validation or Record Level Validation is more flexible than a rule. Also, any undesirable changes can be prevented.
• Referential Integrity: I know it is difficult to digest referential integrity enforced by trigger when we have foreign keys available but I can assure you that sometimes business requirements are so complex that we somehow maintained referential integrity by triggers. Placing a trigger which works on different database is a suitable candidate for trigger.
• Implement a Business rule: Let's see if we want to display a user friendly message when a certain condition is met or requires some additional which is not currently available.

How Can We Compare Before and After Data Values?

These are also called as Magic tables. These tables are temporary and get created only for the duration of the trigger till the results are committed to the database. These tables can be used while creating a trigger as ‘inserted’ and ‘deleted’ and they have the same structure as the original table. Only the data which is being deleted or inserted is kept for temporary usage in these tables. SO for auditing purposes, data between the main table and these temporary tables can be compared.

CREATE TRIGGER myTrigger
ON Employee
AFTER INSERT AS
BEGIN
insert into Department (EmployeeID)
select inserted.EmployeeID from inserted
END

What are the Differences Between Stored Procedures and Triggers?

• Trigger fires automatically on action against a table but stored procedures execute manually.
• Stored Procedure cannot call a trigger but a Trigger can call specific stored procedures.
• Stored Procedures can be called from top layers of architectures but Triggers can’t be called.
• Stored Procedure can take the input parameters, but Triggers can’t.

Trigger Tips Out of Experience

• Avoid using nested triggers: Triggers can be nested up to 32 levels. Do not go till that level as it might hit the performance.
• Multiple triggers allowed on a table for each data modification action.
• Avoid using recursive triggers.
• INSTEAD OF triggers requires knowledge of upcoming schema changes.
• Use sp_settriggerorder to set the trigger order.
• Types like text, ntext, and image columns cannot be referenced in the AFTER trigger

I think the amount of information about trigger is enough to answer few questions which keeps on bugging us. We will include more articles sharing our experience. Please send your response to admin@codespread.com
CodeProject

Related Posts

1. SQL Tables,Temporary Tables,Table Variables
2. SQL:Target a SubQuery
3. SQL Bitwise operator can simplify DB design