Create a data base trigger to not to allow more than four employee in a dept
create trigger trg1
on emp
instead of insert
as
begin
declare @eno int,@ename varchar(20)
declare @dno int, @cnt int
select @eno=empno, @ename=ename,@dno=deptno from inserted
select @cnt=count(*) from emp where deptno=@dno
if @cnt>4
raiserror('max 4 emp',15,1)
else
insert into emp(empno,ename,deptno) values(@eno,@ename,@dno)
end
insert into emp(empno,ename,deptno) values(1129,'dfsa',30)
a program which will restrict the delete operation if the job of the person is manager
create trigger checkmanager
on emp
after delete
as
begin
declare @job varchar(50)
select @job=job from deleted
if @job='Manager'
begin
rollback
raiserror('cannot delete manager from the table',15,1)
end
end
delete from emp where empno=7566
instead of Trigger
are the trigger that are executed instead of executing the given insert, update, and delete statement.
Main Purpose of Instead of Trigger is to allow insert, update, delete on complex view but instead of trigger can be executed on tables also.
Create a complex view that provides details of employee along with deptname and location in which the employee is working and than create an instead of trigger on it to allow insert
Create a complex view that provides access to details of employees along with deptname and location as follows
create view empdept as select E.*,dname,loc from EMP e inner join DEPT d on e.DEPTNO=d.DEPTNO
write the following insert statement on the view empdept which will cause error because of view is complex view
insert empdept values(1001,'a','clerk',7902,'10/21/990',2000,null,50,'computers','hyd')
create an instead of trigger on the view empdept as follows
create trigger allowinsert
on empdept instead of insert
as begin
declare @dno int
select @dno=deptno from inserted
if not exists(select * from dept wheredeptno=@dno)
begin
insert dept select deptno, dname,loc from inserted
end
insert emp select empno,ename,job,mgr,hiredate,sal,comm,deptno from inserted
end
insert empdept(empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc) values(10022,'a','clerk',7902,10/21/990,2000,null,50,'computers','hyd')
Create a trigger which will not allow the user to edit, add or delete from the table
create trigger trg4
on emp
for insert, update, delete
as
begin
print 'record can be viewed'
rollback transaction
end
Getting list of trigger
sp_helptrigger 'emp'
Disable and enable triggers
Alter table <Table name>
Disable or enable trigger <trigger name>
alter table emp
disable triggertrg1
To disable all trigger
alter table emp
disable triggerall
Getting definition of trigger
Sp_helptext ‘triggername’
Delete a trigge
Drop trigger <Triggername>
DDL Triggers
It is introduced from sql server 2005.
These trigger are created to control DDL operations like create, alter, drop
They can be used to perform administrative tasks in the database
Ddl triggers fire only after the ddl statement that trigger them.DDL Trigger cannot be used as instead of triggers,
DDL Trigger fires in 2 different cases likes
Database: applies the scope of DDL Trigger to the current database.
All server: applies the scope of a DDL or logon trigger to the current server.
Syntax:
Create trigger <name>
On db
After create, alter, drop
As
Begin
Statement
End
create trigger safety
on database
AFTER drop_Table,alter_Table
as
begin
raiserror('you cannot drop or modify any table',15,1)
rollback
end
create trigger safety
on ALLSERVER
AFTERCREATE_dATABASE
as
begin
PRINT 'DATABASE CREATED'
rollback
end
Dropping DDL Trigger
Syntax:
Drop trigger <Triggername> on db/allserver
Drop trigger safety on database
Drop trigger ddl_Trg on allserver
create trigger safety1
on database
AFTERcreate_table
as
begin
PRINT 'table CREATED'
rollback
end
How many instead of insert trigger can be created on a table
Answer: one
Nested Triggers:
Trigger can be nested to a maximum of 32 levels
If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger and so on.
To disable nested trigger, set the nested triggers option of sp_configure to zero (off)
