Trigger

In a DBMS, a trigger is a SQL procedure that initiates an action (i.e., fires an action) when an event (INSERT, DELETE or UPDATE) occurs. Since triggers are event-driven specialized procedures, they are stored in and managed by the DBMS. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion.
Each trigger is attached to a single, specified table in the database.
Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.


CREATE TRIGGER [dbo].[TrTermCondition] ON [dbo].[TermCondition]
FOR INSERT, UPDATE, DELETE
AS

DECLARE @VendorID AS INT

    IF EXISTS (SELECT 1 FROM INSERTED)
    BEGIN
        SELECT  @VendorID = VendorID                  
        FROM    INSERTED       
           
        UPDATE    BUYER
        SET        IsSalesTermsAccepted = 0,
                ModifiedDt = getDate(),
                ModifiedUserID = 55   
        FROM    Buyer B               
            INNER JOIN VendorBuyers VB ON VB.BuyerID = B.ID   
           
        WHERE    VB.VendorID = @VendorID                       
    END


Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
The inserted and deleted tables are used primarily in triggers to:

• Extend referential integrity between tables.
• Insert or update data in base tables underlying a view.
• Check for errors and take action based on the error.
• Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.