TRIGGERS

WHAT IS TRIGGER

A trigger is a special kind of stored procedure that is activated ("triggered") in response to a particular event in a database.

trigger is called automatically when a data modification event occurs against a table.

There are different kinds of events that can activate a trigger like inserting or deleting rows in a table, a user logging into a database server instance, an update to a table column, a table is created, altered, or dropped, etc.

3 TYPES OF TRIGGERS

1) DML triggers are automatically fired when an INSERT, UPDATE or DELETE event occurs on a table.

2) DDL triggers are automatically invoked when a CREATE, ALTER, or DROP event occurs in a    database.

3) Logon triggers is invoked when a LOGON event is raised when a user session is established.

WHY WE NEED TRIGGER

Triggers will be helpful when we need to execute some events automatically on certain desirable scenarios. For example, we have a constantly changing table and need to know the occurrences of changes and when these changes happen. If the primary table made any changes in such scenarios, we could create a trigger to insert the desired data into a separate table.

1) FOR Triggers

The FOR triggers can be defined on tables or views. It fires only when all operations specified in the triggering SQL statement have initiated successfully.

2) AFTER Triggers

 The AFTER trigger fires only after the specified triggering SQL statement completed successfully. AFTER triggers cannot be defined on views.

3) INSTEAD OF Triggers

 An INSTEAD OF trigger allows you to override the INSERT, UPDATE, or DELETE operations on a table or view. The actual DML operations do not occur at all.

4) LOGON Triggers

In SQL Server, the Logon trigger is fired automatically on a LOGON event. They are DDL triggers and are created at the server level. We can define more than one LOGON trigger on a server.

ADVANTAGES OF TRIGGERS

• Triggers set database object rules and roll back if any change does not satisfy those rules. The trigger will inspect the data and make changes if necessary.

• Triggers help us to enforce data integrity.

• Triggers help us to validate data before inserted or updated.

• Triggers help us to keep a log of records.

• Triggers increase SQL queries' performance because they do not need to compile each time they  are executed.

• Triggers reduce the client-side code that saves time and effort.

 • Triggers are easy to maintain.

DISADVANTAGES OF TRIGGERS

•Triggers are invoked automatically, and their execution is invisible to the user. Therefore, it isn't easy to troubleshoot what happens in the database layer.

• Triggers may increase the overhead of the database server.

• We can define the same trigger action for multiple user actions such as INSERT and UPDATE in the same CREATE TRIGGER statement.

• We can create a trigger in the current database only, but it can reference objects outside the current database.

--- Created audit table to store DML changes

--- Insert Trigger

---UPDATE TRIGGER

---DELETE TRIGGER

---TRIGEER SAVE CHANGES IN EMPLOYEE AUDIT TABLE

---DDL Trigger

--Trigger to restrict Create Alter Drop