CREATE TRIGGER

CodeProject

Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts -modification statement on the specified table.


Syntax

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}



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 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.



The trigger actions specified in the Transact-SQL statements go into effect when the DELETE, INSERT, or UPDATE operation is attempted.

Triggers can include any number and kind of Transact-SQL statements. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. The Transact-SQL statements in a trigger often include control-of-flow language. A few special tables are used in CREATE TRIGGER statements:

    deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:

    SELECT *
    FROM deleted


Trigger Limitations

CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

If the trigger owner name is specified (to qualify the trigger), qualify the table name in the same way.

The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

Any SET statement can be specified inside a trigger. The SET option chosen remains in effect during the execution of the trigger and then reverts to its former setting.


A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger. Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.



These Transact-SQL statements are not allowed in a trigger:
  
ALTER DATABASE     CREATE DATABASE     DISK INIT
DISK RESIZE     DROP DATABASE     LOAD DATABASE
LOAD LOG     RECONFIGURE     RESTORE DATABASE
RESTORE LOG





Types :

after trigger

instead trigger



after trigger for update Example :


CREATE TABLE [dbo].[product](
    [product_id] [int] NULL,
    [product_name] [varchar](32) NULL,
    [supplier_name] [varchar](32) NULL,
    [unit_price] [int] NULL
)


CREATE TABLE [dbo].[product_price_history](
    [product_id] [int] NULL,
    [product_name] [varchar](32) NULL,
    [supplier_name] [varchar](32) NULL,
    [unit_price] [int] NULL
)


Note : Trigger fired when unit_price column updated---


CREATE TRIGGER price_history_trigger up ON product
for update
As
Declare @ProdID int

Select @ProdID =product_ID from deleted

if UPDATE(Unit_Price)
BEGIN
    INSERT INTO product_price_history(product_id,
    product_name,
    supplier_name,unit_price)
    select * from deleted --where Product_id=@ProdID
End


Example fire

Update Product set Unit_Price=600 where Product_ID=1


output :


(1 row(s) affected)

(1 row(s) affected)



After trigger for --Insert


Note: insert trigger fired when data is inserted in product table




CREATE TRIGGER price_history_triggerins ON product
after insert
As
Declare @ProdID int

Select @ProdID =product_ID from inserted



    INSERT INTO product_price_history(product_id,
    product_name,
    supplier_name,unit_price)
    select * from inserted --where Product_id=@ProdID






Example fire

Insert into product
select 4,'abc1','a',100

output :


(1 row(s) affected)

(1 row(s) affected)