SQL SERVER – 2008 HOW To Work with TRIGGERS

Triggers: Triggers is a type of stored procedure that implicitly executed when user performs DML operation on the table.

It will not accept any parameters

Types of triggers:

  1. Insert trigger
  2. Delete trigger
  3. Update trigger

Syntax: create trigger <trigger_name> for/after, insert/update/delete as sql statement.

Insert Trigger:

This trigger fires when user performs insert operation on the table. When user insert a record into the table the temporary table called Inserted is created.

The newly inserted record is also stored in inserted table temporarily.

->Create trigger

for insert

as

begin

print ‘trigger t1 fire’

end

-> Create trigger t2 on dept

for

as

print ‘trigger t2 fire’

->Create trigger t3 on dept

for insert

as

print ‘trigger t3 fired’

->Insert into dept (deptno) values (50)

trigger t1 fired

trigger t2 fired

trigger t3 fired

Changing the firing order of triggers:

Syntax: sp_settrigger order @ trigger name = ‘name_of_trigger’,

@order = ‘first/last’,

@stmtype = ‘insert/update/delete’

sp_settrigger order      @trigger name = ‘t3’,

@order = ‘first’,

@stmtype = ‘insert’

sp_settrigger order      @trigger name = ‘t1’,

@order = ‘last’,

@stmtype = ‘insert’

insert into dept (deptno) values (60)

trigger t3 fired

trigger t2 fired

trigger t1 fired

Creating table from existing table:

1.With data:

->Select * into dept 1 from dept

2. Without data:

->Select * into dept 2 from dept

->Create table dept_insert from dept

Without data

->Create trigger insert_data

on dept

for insert

as

begin

insert into dept_insert select * from inserted

end

->Insert into dept values (50, ‘edu’,  ‘hyd’)

->Select * from dept_insert

deptno       dname       loc

50             edu           hyd

->select * from dept

deptno       dname       loc

10             ——         —–

20             ——         —–

30             ——         —–

40             ——         —–

50             edu           hyd

Delete trigger:

This trigger fires when users performs delete operations on the table.

When user deletes the records from table a temporary table called deleted is created due to the trigger and deleted data is also stored temporarily in that table.

->create table dept_delete from dept without data

->create trigger deleted_data on dept

for

as

begin

insert into dept_delete select * from deleted

end

->delete from dept where deptno = 10

->select * from dept

->10 dept details will not appears

->select * from dept_delete

deptno       dname               10c

10             Accounting         Newyork

Update trigger:

This trigger fires with update operation when update operation is performed on the table. Two temporary tables, 1.INSERT 2.DELETED are created due to trigger the modified data is stored in the inserted table and old data stored in deleted table.

->truncate table dept_insert

->truncate table dept_delete

->create trigger update_data

on dept

for update

as

begin

insert into dept_insert select * from insert

insert into dept_delete select * from deleted

end

->update dept

set dname = ‘EXPORT”

loc = ‘SEC’ where deptno = 10

->select * from dept_insert

deptno       dname       loc

10             EXPORT     SEC

->select * from dept_delete

deptno       dname               loc

10             Accounting                 Newyork

Instead of Triggers: These trigger are mainly created for views.

Syntax: create trigger trigger_name on <view_name>

Instead of insert/update/delete

As

Sql statement

->create view v10

as

select * from emp where deptno=10

->select * from v10

->it willdisplay an 10th dept employees details.

->create trigger v10_trg

instead of insert

as

update v10 set sal = sal + 500

->insert into v10 (empno, ename, deprno)

values (100, ‘CHAD’, 10)

->select * from v10

->instead records will not appears but salaries are modified due instead of trigger.

->create view dept_view

as

for insert

as

delete from dept_view

->insert into dept_view (deptno) values (60)

->select * from dept_view

datadeleted due to instead of trigger

->create view emp_dept

as

select empno, ename, dept. deptno, dname from emp, dept where emp.deptno = dept. deptno

select * from emp_dept

Empno Ename Deptno Dname

Insert into emp_dept (empnon ename, deptno, dname)

values (100, ‘CHAD”,50, ‘IMPORT”)

ERROR

Since not possible to insert data into two tables through single view

->create trigger emp dept_trg

on emp_dept

for insert

as

begin

insert into emp (empno,ename) select empno, ename from inserted.

Insert into dept (deptno, dname)select deptnp, dname from inserted.

End

->insert into emp_dept (empno, ename, deptno, dname)

values (100, ‘CHAD’, 50, ‘IMPORT’)

->select * from emp

->new record will be displayed along with old records

->select * from dept

->new record will be displayed along with old records.

Displaying the code of Trigger:

Syntax:

Sp_helptext ‘trigger_name’

Sp_helptext ‘emp_dept’

Displaying the triggers information for the table

Sp_helptrigger ‘table_name’

Sp_helptrigger ‘dept’

DCL Commands: these commands are used to grant or revoke the permission on database objects to other users.

1. grant: Used to grant the permissions and data objects to users.

Adminstrator tools

Grant select on dept to ram

Grant insert, select on emp to ram

Dilip/dilip 123 login

Select * from dept

->dept data is displayed

insert into dept values (50, ‘***’, ‘ddd’)

->error since no insert permission to ram

select * from emp

insert into emp (empno, ename) values (400, ‘chris’)

-> 1 record is inserted

update emp set empno = 111 where ename = ‘SMITH’

->error since no update permission to ram

Revoke: used to take back the permissions given to the users

Revoke select on dept from RAM

Revoke select, insert an emp from RAM

Administrator login:

Granting permission with grant option:

Grant select on dept to ram with grant option

Ram login:

Select * from dept

->data will be displayed

grant select on dept to naresh

dilip login:

select * from dept

Administrator login:

Revoke select on dept from ram cascade

->now both ram and naresh will loose the permission on dept.

->sp_who

->it will display the status of logins.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s