Ok, here we go again guys/girls, lets try to learn something smart together. In this tutorial, I’m focusing on triggers. So What are triggers? I like to keep things simple and stupid sometimes so l’ll say triggers are pre-compiled set of T-SQL statements that are automatically executed on a particular Data Definition Language, Data Manipulation Language or log on event. I hope we all know what DML and DDL are? DDL stands for Data Definition Language and DML stands for Data Manipulation. To keep it simple, DDL are TSQL statements to create, alter, or drop structures. DDL are CREATE, ALTER, DROP and finally TRUNCATE is added to the list because of how it behaves. DML are INSERT, UPDATE and DELETE. SELECT actually is just for reading, it doesn’t do any manipulation so its safe to remove it from the list for DML but where are you going to put it, l think that’s why it’s always added to the DML statements. But the select statement has nothing to do with triggers. There are 3 types of triggers, DML triggers, DDL Triggers and Log on triggers.
1. DML Triggers
They are invoked when a DML event like insert, update or delete occur which modify data in a specified table or view. A DML trigger can query other tables and can include complex tsql statements. They can cascade changes through related tables in the database, provide security against malicious or incorrect DML operations and enforce restrictions that are more complex than those defined with constraints. All the constraints we can put on tables can be implemented using a trigger.
There are two options for the DML Triggers, These are; after/for and Instead Of.
1.After/for DML trigger
After triggers are executed after the DML action is performed. That means, first, the DML statement gets executed then the trigger body gets executed. The after trigger can be specified on tables and views and one table or view can have many after triggers for each triggering DML action. For example, a table can have after trigger for insert, one for delete and one for update or one trigger can be defined to be fired when any dml statement is executed. The syntax for creating the after trigger looks like this,
CREATE TRIGGER <triggername> ON <tablename/viewname>
AFTER/FOR <DML Type(s)>
AS
BEGIN
<trigger body goes here
END
Note, Triggers do not have parameters and return statements. Lets create a table and see how trigger works on it.
CREATE TABLE visitor(visitorid int, name nvarchar(50))
INSERT INTO visitor
VALUES(1,’a’),(2,’b’),(3,’c’),(4,’d’)
Now, lets create a trigger to do something on updates or deletes or inserts on this table
CREATE TRIGGER trg_Printer ON visitor
AFTER DELETE
AS
BEGIN
PRINT ‘A Visitor has been removed from the visitor table’
END
After creating this trigger, any time you delete a value, the message will be printed out. This is not any useful example but imagine tweaking this trigger so that it will back up the new records inserted to a back up table for you. We’ll look at an example of this using magic tables when l discuss it in a minute. You can define the trigger for more than one DML at a time by just separating the DML statements with comma and the trigger will work for all of them as defined. Second type of dml triggers is;
2. INSTEAD OF DML trigger.
These triggers are fired instead of the triggering DML action. They can also be specified for views and tables but each table or view can have only one INSTEAD off trigger for each triggering DML action. This statement can get confusing. Simply, the After/for trigger can have multiple of them on the same DML statements but there can be only one INSTEAD OF trigger for each of the DML statement. This is the syntax for creating this type of trigger.
CREATE TRIGGER <triggername> ON <tablename/view name>
INSTEAD OF <DML type(s)>
AS
BEGIN
<trigger body goes here>
END
So lets create an instead off trigger on the visitor table so that when a user tries to insert a new record, the trigger will be fired and so that the visitor will be added and back up will be made to the visitor_backup table.
CREATE TRIGGER trg_backup ON visitor
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO visitor
SELECT * FROM INSERTED
INSERT INTO visitor_backup
SELECT * FROM INSERTED
END
To understand this concept, l have to talk about magic tables. These are tables which take care of our dml statements behind the scene. One is called INSERTED and the other is DELETED. Any time you want to insert a value into a table, the INSERTED magic table holds all that record, weather the insertion you are trying to do is successful or not. Same applies to the DELETED magic table. When you try to delete something, the DELETED magic table will hold that record weather your deletion is successful or not. These tables are visible inside a trigger body so any time an action takes place like insert or delete, the trigger can check the magic tables and retrieve what was going on. In our example above, the trigger went and insert the value that was going to be inserted into the visitor table and insert it into visitor and visitor_backup table. That means, the original insert statement was not executed, the instead off trigger was fired and this trigger took care of everything. During an update, both inserted magic table and deleted magic table gets to work. The deleted magic table will hold the old record in the table to be updated and the inserted table holds the record with the new changes made to it. Sounds cool? ya ya ya. To cut it short ,We can communicate between the external code and trigger body using inserted and deleted magic tables. The structure of inserted or deleted magic tables depends upon the structure of the table in the dml statement. Both of these tables are automatically created by SQL server. Lets work on the following requirement to see how useful a trigger can get.
Requirement.
You have a business that sells iphone, ipad and isomething. Users can buy your stuff by going to your website and placing the orders. You want to make sure that, before an order is successful to the user, the number of item the user wants to purchase is available in stock.Simulate sales transactions by inserting records in sales table. the table stock keeps stock of all products.
Solution
Create a trigger on sales that will automatically update the stock table and update the quantity left in stock.
Eg. If l buy 10 iphones, Insert a record in sales with qty = 10 and pid of iphone, say 100. This insert should trigger an update in stock and update iphone quantity. Assume we have 45 iphones in stock, the new qty will be 45-10 = 35. Also check for availability of items before inserting into the sales talbe. That is , if l try to buy 200 iphones it shouldn’t be allowed because I have only 45 in stock. Lets get our hands dirty at this,
CREATE TRIGGER trg_stockupdate_check_sales ON sales
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO sales
SELECT i.*
FROM inserted i
JOIN stock st
ON i.pid = st.pid
WHERE i.qty <= st.qty
UPDATE st
SET st.qty = st.qty – i.qty
FROM stock st
JOIN inserted i
ON i.pid = st.pid
WHERE i.qty <= st.qty
END
The above example is interesting and should give a better idea of how triggers can work better. Enough of DML, Lets talk about DDL triggers.
DDL triggers
They are database level or server level triggers. They respond to CREATE, ALTER,DROP. The scope of this triggers is database or server level. They support only the after trigger. There is no instead of DDL triggers. You can create it on a database or you create it on the entire server. Here is the syntax
CREATE TRIGGER <trigger name> on <database/all server>
AFTER <DDL event>
AS
BEGIN
<trigger body>
END
The ddl events for this type of trigger have special names ; create_table, drop_table, alter_table, create_index, drop_index. Lets see an example of how to implement the ddl trigger on a database to be fired when a table is dropped
CREATE TRIGGER trg_drop on databasename
AFTER DROP_TABLE
AS
BEGIN
PRINT ‘table dropped’
ROLLBACK –rollbacks current transaction including drop table
END
You see how this trigger works, when a table is dropped say by a malicious person, it rolls back and bring the table back. Isn’t that cool? ya ya ya.
OK, we talked about 2 types of trigger, l just want to mention that you can remove the triggers by dropping them. For a DML trigger, you simple drop like this,
DROP TRIGGER <triggername> ON <table/viewname>
For a DDL trigger, you do it like this
DROP TRIGGER <triggername> ON databasename/all serve
Now lets discuss the last trigger and get out of here lol
Logon trigger
They fire in response to a log on event. This event is raised when a user session is establised with an instance of sql server.
Logon trigger has server scope and will only be fired after the user successfully logged on. You create it on the server. Syntax is as shown below
CREATE TRIGGER <triggername> ON all server
AFTER LOGON
AS
BEGIN
<trigger body>
END
Lets now talk about triggers from performance point of view. One drawback of triggers can be performance problems in a highly transactional system where lot of DML operattions happen, these will result in executing DML as well as executing trigger code. This
can be a potential performance issue if we have a lot of triggers. one can temporarily disable trigger without dropping them and enable them back when they are needed. To disable a trigger, the syntax is shown below
DISABLE TRIGGER <triggername> ON <table/view name>/database/all server
To enable the trigger back to work again, you do it like this
ENABLE TRIGGER <triggername> ON <table/view name>/database/ all server
You can also alter a trigger as show below
ALTER TRIGGER <triggername>ON <table/view name>/database/ all server
AFTER/FOR/INSTEAD OF <DML/DDL/LOGON>
AS
BEGIN
<trigger body>
END
That’s all l have for this topic, please add suggestions, corrections and feedback. Keep in touch, the next discussion in the serious of this tutorial will be partitioning.
