Triggers

Triggers are specail stored procedures that execute automatically based on an event.

There are 3 types of triggers:

1. DML (Data-Manipulating Language) Triggers: work when the event of DMLs are invoked like Insertion, Updating, Deletion etc
2. DDL (Data-Definition Language) Triggers: works when the events of DDL are invoked like Create, Drop, Alter etc
3. Logon triggers: works when the event of logging in and Logging out are invoked.

DML Triggers will be implemented as below:

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

At the place of after in the above example following can be used:

1. After: Event will be triggered after the insert is done
2. Before: Event will be triggered before the insert is done
3. Instead of: this will skip the insert and perform the given instructions. this is like a function override for insert or what ever you will use.

DDL Triggers will be implemented as below:

CREATE TRIGGER alert_table
ON Database
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
  insert into logs values('Table Created'); 

To Disable a trigger:

User Disable trigger <Trigger name> on Database.table name