Triggers
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Triggers as PDF for free.
More details
- Words: 912
- Pages: 6
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 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 10 -----20 -----30 -----40 -----50 edu
loc ----------------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 10 EXPORT
loc SEC
select * from dept_delete deptno dname 10 Accounting
loc Newyork
Instead of Triggers: These trigger are mainly created for views. Syntax: create trigger trigger_name on 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 Ram/ram 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 naresh 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.
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 10 -----20 -----30 -----40 -----50 edu
loc ----------------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 10 EXPORT
loc SEC
select * from dept_delete deptno dname 10 Accounting
loc Newyork
Instead of Triggers: These trigger are mainly created for views. Syntax: create trigger trigger_name on
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 Ram/ram 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 naresh 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.
Related Documents
Triggers
May 2020 15
Triggers
November 2019 38
Triggers
June 2020 12
Triggers
May 2020 17
14 Triggers
November 2019 30