Triggers in SQL Server

Database Trigger:

Database trigger is a stored procedure that is fired when an insert,update or delete statement is issued against the associated table.

uses:
1.To generate data automatically.
2.To enforce complex integrity constraints(checking with sysdate,checking with data in another table)
3.To customize complex security authorization
4.To maintain replicate tables.
5.To audit data modification

    bank_master 
accno    balance
1001    5000
1002    20000
1003    25000
1004    40000
1005    55230

    bank_transaction
cname    accno    ttype    amount
xxx         1003    db        5000
yyy         1005    cr         5230
zzz          1001    db        5000
zzz          1001    db        15000

syntax for creating a trigger:

create/alter trigger <trigger_name>
on <tablename>
after/instead of/for insert,update,delete
as
begin
statements
end

CREATE TRIGGER T11
ON BANK_TRANSACTION
AFTER INSERT
AS
BEGIN
DECLARE C1 CURSOR FOR SELECT * FROM BANK_TRANSACTION
DECLARE @CNAME VARCHAR(20),@ACCNO INT,@TTYPE VARCHAR(2),@AMOUNT INT
OPEN C1
FETCH NEXT FROM C1 INTO @CNAME,@ACCNO,@TTYPE,@AMOUNT --START THE LOOP
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM C1 INTO @CNAME,@ACCNO,@TTYPE,@AMOUNT -- CONTINUE THE LOOP
END
IF @TTYPE='CR'
UPDATE bank_MASTER SET BALANCE=BALANCE +@AMOUNT WHERE ACCNO=@ACCNO
ELSE
UPDATE bank_MASTER SET BALANCE=BALANCE -@AMOUNT WHERE ACCNO=@ACCNO
CLOSE C1
DEALLOCATE C1
END

create trigger t5
on student
after insert
as
begin
DECLARE C1 CURSOR
FOR SELECT SNO,S1,S2,S3 FROM STUDENT
DECLARE @SNO INT,@S1 INT,@S2 INT,@S3 INT
DECLARE @STOT INT ,@SAVG INT
DECLARE @SRES VARCHAR(4)

OPEN C1

FETCH NEXT FROM C1 INTO @SNO,@S1,@S2,@S3
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @STOT=@S1+@S2+@S3
SET @SAVG=CEILING((@STOT)/3)
IF @S1 >35 AND @S2 >35 AND @S3 > 35
SET @SRES='PASS'
ELSE
SET @SRES='FAIL'
INSERT INTO RESULT VALUES(@SNO,@STOT,@SAVG,@SRES);
FETCH NEXT FROM C1 INTO @SNO,@S1,@S2,@S3
END
CLOSE C1
DEALLOCATE C1
end

 create a trigger tr1 for INSERT,DELETE triggering event where trigger should be fired if the transactions are performed on SUNDAY?
CREATE TRIGGER tr1
ON bank_master
FOR INSERT,DELETE
AS
BEGIN
  IF DATENAME(DW,GETDATE())='Sunday'
  BEGIN
    ROLLBACK
    RAISERROR('CANT INSERT OR DELETE THE DATA ON SUNDAY',1,1)
  END
END

Create a trigger tr2 For INSERT,DELETE Triggering event where trigger should be fired if the transaction
is performed before 10AM and After 5PM?

CREATE TRIGGER tr2
ON bank_master
FOR INSERT,DELETE
AS
BEGIN
    IF DATEPART(HH,GETDATE()) not in(10,11,12,13,14,15,16,17)
    BEGIN
        ROLLBACK
        RAISERROR('INVALID TIME',1,1)
    END
END

CREATE TRIGGER T1 ON EMP AFTER INSERT,UPDATE,DELETE
AS
BEGIN
IF DATEPART(DW,GETDATE())=1
BEGIN
ROLLBACK
RAISERROR('SUNDAY NOT ALLOWED' ,15,1)
END
END

CREATE TRIGGER T2 ON EMP
AFTER INSERT,UPDATE,DELETE
AS BEGIN
IF DATEPART(HH,GETDATE() )< 10
BEGIN
ROLLBACK
RAISERROR('INVALID TIME',15,1)
END
END

CREATE a trigger tr4 for DELETE triggering event on DEPT table,where trigger should be fired by deleting the records from emp table?

CREATE TRIGGER TR7
ON DEPT
FOR DELETE
AS
BEGIN
    DELETE FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DELETED)
    PRINT CAST((@@ROWCOUNT AS VARCHAR(5))+ 'ROWS ARE DELETED'
END

Difference between procedures and Triggers:

procedures                                             Triggers
1.called Explicitly                                   1.called implicitly
2.can be created without a table.            2.can't be created without a table.
3.it accepts parameters                          3.it won't accept parameters
4.it returns a value                                 4.it won't return a value.

VIRTUAL TABLES (MAGIC TABLES)
    SQL SERVER provides 2 virtual tables which can be used only in triggers. These tables are provided by TempDB(System database).These tables play major role to have backup of data.

Tables are Identified as INSERTED , DELETED

INSERTED :
    This table will store the same data which is provided in target table(user defined table).   
    If a record is inserted into a target table then the same record is available in this virtual table

Data avaiable in this table can be used to perform operations and can be again stored into the other
 user defined tables.
    If the record is updated in a target table, new value is stored in this magic table and old value is transfered to DELETED Table.

DELETED :
    This magic table stores the removed rows from a target table.It also stores the old value when update operation is performed over a target table.
   
note:
These tables will store the data for temporary.
These tables can be used at once in a single trigger.

INSTEADOF TRIGGERS:
    These triggers is used to make the modifications into base table thru a complex view.(Non updateable view)

    By default a complex view is not updatable view (i.e. read only view). A complex view consists of joins,mathematical expressions, group by clause, group functions, distinct operator.

examples:

create a complex view on emp table that stores a query for empno,sal and annual salary?
CREATE VIEW V1
AS
SELECT EMPNO,SAL M_SAL,SAL*12 A_SAL FROM EMP

CREATE TRIGGER tr6
ON v1
INSTEAD OF INSERT
AS
BEGIN
   INSERT EMP(EMPNO,SAL) SELECT EMPNO,M_SAL FROM INSERTED
end

create a database trigger do not allow to enter more than four employees in a emp4 table.
create trigger tr1 on
emp4
instead of insert
as
begin
declare @cnt int
declare @ename varchar(20),@eno int,@salary int,@deptno int
select @ename=ename,@eno=eno,@salary=salary,@deptno=deptno from
inserted
select @cnt=count(*) from emp4 where deptno=@deptno
if @cnt=4
raiserror('max 4 employees',15,1)
else
insert into emp4 values(@ename,@eno,@salary,@deptno)
end

select * from emp4

Create a trigger tr4 for DELETE triggering event on DEPT table, where trigger should be fired by deleting the records from emp table?
CREATE TRIGGER TR4
ON DEPT
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DELETE FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DELETED)
    PRINT CAST(@@ROWCOUNT AS VARCHAR(5))+' Rows Are Deleted'
END

Create a trigger for DDL triggering event that restricts the dropping and altering of a table in a database?

CREATE TRIGGER tr5
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
   ROLLBACK
   RAISERROR('CANT ALTER OR DROP THE TABLES',15,16)
END

To display list of triggers created in a database:
* SELECT NAME FROM SYSOBJECTS WHERE XTYPE='TR'

* select * from sys.all_objects where type='tr'

VIEWING TRIGGER INFORMATION:
SP_HELPTEXT trigger_name

SP_HELPTEXT tr2

DISABLE / ENABLE THE TRIGGERS:
ALTER TABLE table_name
DISABLE/ENABLE TRIGGER trigger_name

DROPING A TRIGGER:

DROP TRIGGER trigger_name

Thanks
GSV