SQL:Target a Trigger

SQL-Tables
SQL-Tables

SQL:Target a Trigger

Welcome to CodeSpread!

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 a 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 it looks like?

Trigger

Trigger

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 happens 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 the perform additional actions before insert, 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 lot of theory is still pending. We might include a tutorial on this topic in near future. Now, we move to DDL triggers. There are two types of DDL triggers.
  • Transact-SQL DDL Trigger

A special type of Transact-SQL stored procedure that executes one more 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.

  • 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 requires 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 purpose 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 : Lets 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 gets 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 kept for temporary usage in these tables. SO for auditing purpose, 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 executes manually.
  • Stored Procedure can not 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

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

2 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More from CodeSpread:

  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • SQL: Usage of char, varchar and varchar(MAX)SQL: Usage of char, varchar and varchar(MAX)These are character data types in SQL. We will take an example and describe each type. Later we determine the cases where one of them can be applied. [code] Create Table testable( Name char(20...
  • Styles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptStyles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptDevelopers take months to write beautiful pieces of code and reviewer gets only few hours or I say, minutes to provide suggestions and improvisations. Then it becomes a hard deal for a developer to...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...