What is the trigger and explain the types of triggers?

A trigger is an operation that is executed when some kind of event occured in database. It can be an object or data change.

Types of triggers

DML Triggers

     After Triggers(only on table)

     Instead of triggers(table and views)

DDL Triggers

SQL CLR triggers

 After Trigger: If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

Ex

create trigger trg1 on emp

after insert,update,delete

as

begin

if datename(dw,getdate())='sunday'

begin

rollback

raiseerror('Today is sunday',15,1)

end

end

Instead of trigger : If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

CREATE TRIGGER INSTEADOF_TR_I_EmpQualification 
ON vw_EmpQualification
INSTEAD OF INSERT AS
BEGIN

DECLARE @Code TINYINT
SELECT @Code = qualificationCode 
FROM lib_Qualification L INNER JOIN INSERTED I
ON L.qualification = I.qualification

IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END

INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 
SELECT empcode, name, designation, @code, 0 
FROM inserted 
END
GO

Rules of triggers

cannot create or modify dbobjects.

cannot perform administrative tasks

cannot pass any parameters

cannot directly call triggers.

Advantages of triggers

-----------------------

Triggers are useful for auditing data changes or auditing database as well as managing the business rules.

Can access both new values and old values in data base when going to do any insert, update and delete.

Disadvantages of triggers

Triggers hide db operations

It effects performance

DDL triggers can be created in database or server. If you want to monitor table creations and drops you can use ddl triggers