Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Trigger

  1. #1
    Join Date
    Jun 2007
    Posts
    44

    Unanswered: Trigger

    i am not sure what do i create, an insert trigger or update trigger. i have a( TABLE A) table where there are lots of updates goes on, so i want to create a trigger, if any updates takes place into that table it will make changes to TABLE B( table b has same columns as table A) and updates one of the column for that row ( which is date field) to get current date.

    any help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please post the DDL for the tables.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jun 2007
    Posts
    44
    table just have ( prod_id #,company_name,price,data) field. now i nees new table with same attributes but i need to capture the price changed in old table and update the new table with new price and date.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    happy turkey day. Gobble Gobble.

    Code:
    CREATE TRIGGER [trig_UID_MyTable] ON [dbo].[MyTable] 
    FOR INSERT, UPDATE, DELETE 
    AS 
    							
    IF EXISTS (SELECT * FROM INSERTED) 
    BEGIN
    
    INSERT INTO [DBO].[MyTableHistory] (ID,COL1,COL2,[ACTION]) 
    SELECT orig.ID, orig.COL1,orig.COL2,'insert\update'
    FROM [MyTable] orig 
    INNER JOIN INSERTED i 
    ON orig.[ID] = i.[ID]
    
    END
    ELSE IF EXISTS (SELECT * FROM DELETED) 
    BEGIN
    
    INSERT INTO [DBO].[MyTableHistory] (ID,COL1,COL2,[ACTION]) 
    SELECT d.ID,d.COL1,d.COL2,'DELETE' 
    FROM DELETED d
    
    END
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2007
    Posts
    44
    [QUOTE][/
    CREATE TRIGGER [dbo].[Update_trigger]
    ON [old_table]
    AFTER UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;
    update new_table

    set new_table.price= T.price from
    UPDATED U inner join old_table T on U.id=T.id
    QUOTE]


    Does this makes sense

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uhm.....what is "UPDATED"?
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jun 2007
    Posts
    44
    we can't use table updated? is it have to be like delete and insert trigger insetead of update trigger?

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you need to read this and get back to us. For update statements the old values are in the deleted table and the new ones are in the inserted table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jun 2007
    Posts
    44
    i donot care about deleted values, all i need in new table is what has been updated and put the updated date field in new table.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by drek_01
    i donot care about deleted values,
    Yes you do, because updated records are identified by the fact that they exist in both the inserted and deleted tables. You need to join both of those tables to find the updated values.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jun 2007
    Posts
    44
    CREATE TRIGGER [trig_UID_MyTable] ON [dbo].[MyTable]
    FOR INSERT, UPDATE, DELETE
    AS

    IF EXISTS (SELECT * FROM INSERTED)
    BEGIN

    INSERT INTO [DBO].[MyTableHistory] (ID,COL1,COL2,[ACTION])
    SELECT orig.ID, orig.COL1,orig.COL2,'insert\update'
    FROM [MyTable] orig
    INNER JOIN INSERTED i
    ON orig.[ID] = i.[ID]

    END
    ELSE IF EXISTS (SELECT * FROM DELETED)
    BEGIN

    INSERT INTO [DBO].[MyTableHistory] (ID,COL1,COL2,[ACTION])
    SELECT d.ID,d.COL1,d.COL2,'DELETE'
    FROM DELETED d

    END

    thanks, Happy holidays
    Last edited by drek_01; 12-22-08 at 16:04.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And a Happy New Year.

    Your code is not going to do what you want it to do.

    Try this:
    Code:
    CREATE TRIGGER [trig_UID_MyTable] ON [dbo].[MyTable]
    FOR INSERT, UPDATE, DELETE
    AS
    
    SELECT	coalesce(INSERTED.ID, DELETED.ID),
    	coalesce(INSERTED.COL1, DELETED.COL1),
    	coalesce(INSERTED.COL2, DELETED.COL2),
    	case when DELETED.ID is null then 'Insert'
    		when INSERTED.ID is null then 'Delete'
    		else 'Update' end
    FROM	INSERTED
    	full outer join DELETED on INSERTED.ID = DELETED.ID
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Jun 2007
    Posts
    44
    Quote Originally Posted by blindman
    And a Happy New Year.

    Your code is not going to do what you want it to do.

    Try this:
    Code:
    CREATE TRIGGER [trig_UID_MyTable] ON [dbo].[MyTable]
    FOR INSERT, UPDATE, DELETE
    AS
    
    SELECT	coalesce(INSERTED.ID, DELETED.ID),
    	coalesce(INSERTED.COL1, DELETED.COL1),
    	coalesce(INSERTED.COL2, DELETED.COL2),
    	case when DELETED.ID is null then 'Insert'
    		when INSERTED.ID is null then 'Delete'
    		else 'Update' end
    FROM	INSERTED
    	full outer join DELETED on INSERTED.ID = DELETED.ID
    Blind man, not sure i got this. appreciate little bit more elaboration. thanks

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, since you are inner joining to the inserted and deleted tables, it is not necessary to check whether data exists in them.

    Second, an update consists of both an insert and a delete, so both virtual tables would store the record and you would get two inserts into MyTableHistory for each updated record. And you would not be able to differentiate between the updates and the inserts.
    Using the single statement with coalesce as in my example, you positively identify each record transaction as either and update, insert, or delete, and log a single record in your history table (You will need to change my SELECT statement into an INSERT statement, sorry....).
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what exactly don't you understand?

    that sql is pretty straight up vanilla.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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