Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Post Unanswered: instead of triggers

    pls can somebody give me the syntax of instead of triggers and its usage
    and tell me where it is applicable

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: instead of triggers

    Originally posted by bhargavi
    pls can somebody give me the syntax of instead of triggers and its usage
    and tell me where it is applicable
    From BOL:

    Designing INSTEAD OF Triggers
    The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed.

    An INSTEAD OF trigger can take actions such as:

    Ignoring parts of a batch.


    Not processing a part of a batch and logging the problem rows.


    Taking an alternative action if an error condition is encountered.


    Note INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with a DELETE or UPDATE action.


    Coding this logic as part of an INSTEAD OF trigger prevents all applications accessing the data from having to reimplement the logic.

    In the following sequence of Transact-SQL statements, an INSTEAD OF trigger updates two base tables from a view. In addition, two approaches to handling errors are shown:

    Duplicate inserts to the Person table are ignored, and the information from the insert is logged in the PersonDuplicates table.


    Inserts of duplicates to the EmployeeTable are turned into an UPDATE statement that retrieves the current information into the EmployeeTable without generating a duplicate key violation.
    The Transact-SQL statements create two base tables, a view, a table to record errors, and the INSTEAD OF trigger on the view. These tables separate personal and business data and are the base tables for the view:

    CREATE TABLE Person
    (
    SSN char(11) PRIMARY KEY,
    Name nvarchar(100),
    Address nvarchar(100),
    Birthdate datetime
    )

    CREATE TABLE EmployeeTable
    (
    EmployeeID int PRIMARY KEY,
    SSN char(11) UNIQUE,
    Department nvarchar(10),
    Salary money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
    )

    This view reports all relevant data from the two tables for a person:

    CREATE VIEW Employee AS
    SELECT P.SSN as SSN, Name, Address,
    Birthdate, EmployeeID, Department, Salary
    FROM Person P, EmployeeTable E
    WHERE P.SSN = E.SSN

    You can record attempts to insert rows with duplicate social security numbers. The PersonDuplicates table logs the inserted values, the name of the user who attempted the insert, and the time of the insert:

    CREATE TABLE PersonDuplicates
    (
    SSN char(11),
    Name nvarchar(100),
    Address nvarchar(100),
    Birthdate datetime,
    InsertSNAME nchar(100),
    WhenInserted datetime
    )

    The INSTEAD OF trigger inserts rows into multiple base tables from a single view. Attempts to insert rows with duplicate social security numbers are recorded in the PersonDuplicates table. Duplicate rows in the EmployeeTable are changed to update statements.

    CREATE TRIGGER IO_Trig_INS_Employee ON Employee
    INSTEAD OF INSERT
    AS
    BEGIN
    SET NOCOUNT ON
    -- Check for duplicate Person. If no duplicate, do an insert.
    IF (NOT EXISTS (SELECT P.SSN
    FROM Person P, inserted I
    WHERE P.SSN = I.SSN))
    INSERT INTO Person
    SELECT SSN,Name,Address,Birthdate,Comment
    FROM inserted
    ELSE
    -- Log attempt to insert duplicate Person row in PersonDuplicates table.
    INSERT INTO PersonDuplicates
    SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
    FROM inserted
    -- Check for duplicate Employee. If no duplicate, do an insert.
    IF (NOT EXISTS (SELECT E.SSN
    FROM EmployeeTable E, inserted
    WHERE E.SSN = inserted.SSN))
    INSERT INTO EmployeeTable
    SELECT EmployeeID,SSN, Department, Salary,Comment
    FROM inserted
    ELSE
    --If duplicate, change to UPDATE so that there will not
    --be a duplicate key violation error.
    UPDATE EmployeeTable
    SET EmployeeID = I.EmployeeID,
    Department = I.Department,
    Salary = I.Salary,
    Comment = I.Comment
    FROM EmployeeTable E, inserted I
    WHERE E.SSN = I.SSN
    END

  3. #3
    Join Date
    Apr 2004
    Posts
    11

    Re: instead of triggers

    Originally posted by harshal_in
    From BOL:

    Designing INSTEAD OF Triggers
    The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed.

    An INSTEAD OF trigger can take actions such as:

    Ignoring parts of a batch.


    Not processing a part of a batch and logging the problem rows.


    Taking an alternative action if an error condition is encountered.


    Note INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with a DELETE or UPDATE action.


    Coding this logic as part of an INSTEAD OF trigger prevents all applications accessing the data from having to reimplement the logic.

    In the following sequence of Transact-SQL statements, an INSTEAD OF trigger updates two base tables from a view. In addition, two approaches to handling errors are shown:

    Duplicate inserts to the Person table are ignored, and the information from the insert is logged in the PersonDuplicates table.


    Inserts of duplicates to the EmployeeTable are turned into an UPDATE statement that retrieves the current information into the EmployeeTable without generating a duplicate key violation.
    The Transact-SQL statements create two base tables, a view, a table to record errors, and the INSTEAD OF trigger on the view. These tables separate personal and business data and are the base tables for the view:

    CREATE TABLE Person
    (
    SSN char(11) PRIMARY KEY,
    Name nvarchar(100),
    Address nvarchar(100),
    Birthdate datetime
    )

    CREATE TABLE EmployeeTable
    (
    EmployeeID int PRIMARY KEY,
    SSN char(11) UNIQUE,
    Department nvarchar(10),
    Salary money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
    )

    This view reports all relevant data from the two tables for a person:

    CREATE VIEW Employee AS
    SELECT P.SSN as SSN, Name, Address,
    Birthdate, EmployeeID, Department, Salary
    FROM Person P, EmployeeTable E
    WHERE P.SSN = E.SSN

    You can record attempts to insert rows with duplicate social security numbers. The PersonDuplicates table logs the inserted values, the name of the user who attempted the insert, and the time of the insert:

    CREATE TABLE PersonDuplicates
    (
    SSN char(11),
    Name nvarchar(100),
    Address nvarchar(100),
    Birthdate datetime,
    InsertSNAME nchar(100),
    WhenInserted datetime
    )

    The INSTEAD OF trigger inserts rows into multiple base tables from a single view. Attempts to insert rows with duplicate social security numbers are recorded in the PersonDuplicates table. Duplicate rows in the EmployeeTable are changed to update statements.

    CREATE TRIGGER IO_Trig_INS_Employee ON Employee
    INSTEAD OF INSERT
    AS
    BEGIN
    SET NOCOUNT ON
    -- Check for duplicate Person. If no duplicate, do an insert.
    IF (NOT EXISTS (SELECT P.SSN
    FROM Person P, inserted I
    WHERE P.SSN = I.SSN))
    INSERT INTO Person
    SELECT SSN,Name,Address,Birthdate,Comment
    FROM inserted
    ELSE
    -- Log attempt to insert duplicate Person row in PersonDuplicates table.
    INSERT INTO PersonDuplicates
    SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
    FROM inserted
    -- Check for duplicate Employee. If no duplicate, do an insert.
    IF (NOT EXISTS (SELECT E.SSN
    FROM EmployeeTable E, inserted
    WHERE E.SSN = inserted.SSN))
    INSERT INTO EmployeeTable
    SELECT EmployeeID,SSN, Department, Salary,Comment
    FROM inserted
    ELSE
    --If duplicate, change to UPDATE so that there will not
    --be a duplicate key violation error.
    UPDATE EmployeeTable
    SET EmployeeID = I.EmployeeID,
    Department = I.Department,
    Salary = I.Salary,
    Comment = I.Comment
    FROM EmployeeTable E, inserted I
    WHERE E.SSN = I.SSN
    END

    can u pls give me a simple example for an instead of trigger
    using a single table and wat happens wen u update delete r insert
    let this table not refer to any other table or view

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did the teacher give any examples during lecture?

    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    11

    Post

    no i am jus' learning it on my own
    so pls just a simple example wld help me a lot

  6. #6
    Join Date
    Dec 2002
    Location
    Amsterdam
    Posts
    12
    Originally posted by bhargavi
    no i am jus' learning it on my own
    so pls just a simple example wld help me a lot
    if you don't even know where to apply instead of triggers, how can this knowledge "help you a lot"? Better ask your teacher ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •