Closure
Closure
Home » Databases » What are triggers?

What are triggers?

What are triggers?
, , Leave a comment
What are triggers?
Latest posts by Stratos Matzouranis (see all)

Do they have overhead? Where is their utility? and finally how are they made?

There are 3 different types: DDL triggers for CREATE / DROP actions, AFTER LOGON triggers for when a user logs in DML triggers for UPDATE, Insert, DELETE actions.

In the article we will deal with DML triggers.

What are DML triggers?

It is a special procedure that runs when a user tries to do a DML action (UPDATE, INSERT, DELETE) on the created table or view.

Its purpose when activated varies, we may want it to raise an error, send an email, log records to another table and many more.

How are they made?

We create a table with 3 entries:

IF NOT EXISTS(select * from sysobjects where name='test')
create table test ( onoma varchar(10), tilefono varchar(20))
GO

insert into test values('Stratos','210101010′)

insert into test values('Georgos','213920193′)

insert into test values('Nikos','210101010′)
Commands completed successfully.
(1 row affected)
(1 row affected)
(1 row affected)
Total execution time: 00:00:00.059
select onoma,tilefono from test;
What are triggers?

We create a table that will be filled by the trigger:

IF NOT EXISTS(select * from sysobjects where name='triggerINFO')
create table triggerINFO(onoma varchar(100),tilefono varchar(100),pote_Svistike datetime)
Commands completed successfully.
Total execution time: 00:00:00.013

It's time to make our triggers.

When creating a trigger we must always take into account the 3 cases depending on which action we want it to activate:

/*

For INSERT operation, the INSERTED table will store the records being inserted

for DELETE operation, the DELETED table will store the records being deleted

for UPDATE operation, the DELETED table will store the original value of the records being updated and INSERTED stores the new value

*/
create trigger triggerfordelete
on dbo.test 
for delete
as
insert into triggerinfo(onoma,tilefono,pote_Svistike)
select d.onoma,d.tilefono,SYSDATETIME()
from deleted d
where  tilefono like '210%'
Commands completed successfully.

Total execution time: 00:00:00.019

Now that we have made it, let's test it by deleting a record from the table:

delete from test where onoma='Stratos'
(1 row affected)
(1 row affected)
Total execution time: 00:00:00.012

We see we have the value that the user deleted stored in the other table:

select * from triggerinfo
(1 row affected)
Total execution time: 00:00:00.016
What are triggers?

Let's make another one in the update table:

create trigger triggerforupdate
on dbo.test 
for update
as
insert into triggerinfo(onoma,tilefono,pote_Svistike)
select d.onoma,d.tilefono,SYSDATETIME()
from deleted d
Commands completed successfully.
Total execution time: 00:00:00.018

Let's do our test:

update test set tilefono = '2251020491' where onoma = 'Georgos'
(1 row affected)
(1 row affected)
Total execution time: 00:00:00.005

As you will see in this example it produced 2 results. Why;;;

Because very simply, the first result has the value recorded before it is changed in the INFO table by the trigger.

In the second line you find the result of the update:

select * from triggerinfo
(2 rows affected)
Total execution time: 00:00:00.016
What are triggers?

As we can see, this time it kept the value that the table had before it was updated due to the selection in the trigger for update.

Triggers are useful tools but like most features it comes with overhead and it's not a little.

Share it

Leave a reply