Triggers are nothing but database objects which are used to monitor for any action such as insert/update/delete for a particular table. When this action happends in that table, this trigger is fired.

It is optional that trigger may be used for any or all of the following:

• INSERT INTO
• UPDATE TABLE
• DELETE FROM

Triggers may be used as any of the following

• AFTER
• Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks (like foreign keys) also must succeed before this trigger executes.
• AFTER is the default, if FOR is the only keyword specified.
• AFTER triggers cannot be defined on views.
• INSTEAD OF
• Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.
• At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.
• INSTEAD OF triggers are not allowed on updateable views WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to an updateable view WITH CHECK OPTION specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger.

Implementation of Triggers:

To implement triggers, we have to retrieve the data that was previously in the table before the sql query was executed and the new data inserted or updated by the query.

for this we will have this assumption. If an update query is executed which results in this trigger, then the data in the table before execution of this query is known as "deleted". For this data seems to be deleted after the update query is executed. Similarly, a new set of data will be updated into the row by the update query. This new set of data is known as "inserted".

so, "select * from inserted" and "select * from deleted" are the two queries to find the old and new values in the table from the trigger.

So, depending on the data here, we can finalize with our requirements whether we can commit this change or roll back this change.

A simple example is as below:

  CREATE TRIGGER trig1
  on authors
  FOR INSERT, UPDATE, DELETE
  AS
  
      SELECT a.au_lname, a.au_fname, x.info
      FROM authors a INNER JOIN second_table x 
			ON a.au_id = x.au_id
  
  GO