Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs down Unanswered: TRIGGER action for eace row

    I need to define a TRIGGER on a table (temp1) that executes the triggered action for each row of the subject table(temp1) that the triggering SQL modifies. If the triggering SQL operation does not modify any rows, the triggered action is not executed.

    E.g., If SQL modifies 100 rows, trigger executes 100 times.

    How can i achieve this type of Trigger in SQL Server .

    In DB2 i can acheive this by specifying 'FOR EACH ROW' clause

    Kindly suggest me
    Last edited by sushma007; 09-21-07 at 13:32.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Excerpt from BOL:

    Creating a Trigger


    Before you create a trigger, consider that:
    • The CREATE TRIGGER statement must be the first statement in the batch. All other statements that follow in that batch are interpreted as part of the definition of the CREATE TRIGGER statement.
    • Permission to create triggers defaults to the table owner, who cannot transfer it to other users.
    • Triggers are database objects, and their names must follow the rules for identifiers.
    • You can create a trigger only in the current database, although a trigger can reference objects outside of the current database.
    • A trigger cannot be created on a temporary or system table, although triggers can reference temporary tables. System tables should not be referenced; use the Information Schema Views instead. For more information, see Information Schema Views.
    If you positively cannot perform set based operations, you will have to use a cursor.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why would you ever need a trigger on a temp table

    What DML operation against a temp table would you like to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sushma, you do NOT want to execute your trigger 100 times.
    You want to execute it ONCE against the entire set of modified records.
    Read up on Triggers in Books Online and you will see how to do this using the virtual INSERTED and DELETED tables.
    Yes, DB2 and Oracle triggers fire for each row, but they are loopy systems. Set-based operations beat cursor/loop operations hands down.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Yes, DB2 and Oracle triggers fire for each row, but they are loopy systems.
    It appears that way, but in reality it probably isn't

    You have to excuse the blind dude....Oracle left a bad tats in his mouth

    Cursor pointers...what a beautiful thing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, they say they do, so I just took Oracle's word for it. My mistake, perhaps.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2007
    Posts
    64
    Thanks for your reply ,

    We are in the process of migrating our DB2 database to SQL Server . In DB2 i have the above (FOR EACH ROW) type of trigger used . So is there any equivalent for that type of Triggering action .

    Below is an example of DB2 Trigger in our Database .

    CREATE TRIGGER STrigger
    AFTER INSERT ON S
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    UPDATE S SET A=A+1;
    UPDATE S SET A=A+1;
    end
    I need SQL Server equivalent for this

    P.S just for example sake i have mentioned the table name as temp .From now on consider it as temp1

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    CREATE TRIGGER STrigger
       ON S
       AFTER INSERT
    AS
    update	S
    set	A=A+1
    from	S
    	inner join INSERTED on S.[PrimaryKey] = INSERTED.[PrimaryKey]
    Go
    Substitute the primary key from your table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Aug 2007
    Posts
    64
    I can't do this , because the TRIGGER body can be any thing . I want to get a generic solution for this .

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is possible to write a trigger that will process a single row at a time, but it is the worst possible solution for your problem. The only reason that you should ever consider this is after you have eliminated every other solution.

    Recoding the triggers to operate on sets of rows is far easier than fixing the performance and possible logic problems caused by recoding the triggers to deal with individual rows.

    -PatP

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by sushma007
    I can't do this , because the TRIGGER body can be any thing . I want to get a generic solution for this .

    And what exactly does that mean?

    I do DB2 z/OS, Oracle and SQL Server so I know all the flavors (Cheery garcia a particular favorite)

    So what do you need to do, there is no generic
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sushma007
    I can't do this , because the TRIGGER body can be any thing . I want to get a generic solution for this .
    No problem:
    Code:
    CREATE TRIGGER STrigger
       ON S
       AFTER INSERT
    AS
    [Do stuff with INSERTED and/or DELETED tables.]
    Go
    See? That was easy!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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