Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: problem in update trigger

    Hi all

    i ve the following test table with 3 columns,

    Code:
    create table test (col1 int, col2 int, col3 datetime)
    Whenever any row of this table is updated i want the respective column col3 to be updated with the current time, to accomplish this I wrote the following trigger,

    Code:
    create trigger trig_test on test for update
    as
    
    update test set col1 = inserted.col1, col2 = inserted.col2, col3 = getdate()
    
    go
    when i tried to compile the above trigger i got the following error

    Code:
    Server: Msg 107, Level 16, State 2, Procedure trig_test, Line 7
    The column prefix 'inserted' does not match with a table name or alias name used in the query.
    Server: Msg 107, Level 16, State 1, Procedure trig_test, Line 7
    The column prefix 'inserted' does not match with a table name or alias name used in the query.
    any help to resolve the above issue or any other simple way to accomplish my need,

    thanks in advance,

    Sn

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Code:
    CREATE TRIGGER trig_test 
    ON test FOR UPDATE
    AS
    DECLARE @A INT,
    @B INT
    SET @A=(SELECT COL1 FROM INSERTED)
    SET @B=(SELECT COL2 FROM INSERTED)
    UPDATE test SET col1 = @A, col2 = @B, col3 = getdate()
    
    go
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    is the bigger problem not having a where clause and updating every row in the 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.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    yes,he should use the where clause according to his requirement to update a single row...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    This will work...

    Code:
    CREATE TRIGGER Trigger_Test_Update ON Test
    FOR UPDATE
    AS
    BEGIN
    
    UPDATE Test SET col1=I.col1, col2=I.col2, col3=getdate() 
                FROM Inserted I JOIN Test T
                ON T.col1=I.col1 and T.col2=I.col2 
    END
    -- Example : Run from query analyzer
    Update Test set col2=50 where col1=1
    Last edited by rajeshpatel; 11-06-06 at 02:41.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  6. #6
    Join Date
    Apr 2005
    Posts
    127
    Hi Rudra and Thrasymachus,

    Thanks for the reply,. I tried your solutions and it worked great. However, i wrote the following trigger to handle the time update of a table column in case of multiple rows update, it compiled successfully, oops it failed to execute when i updated some rows in my test table,..

    Code:
    create trigger trig_test on test 
    for update
    as
    
    declare @ins_col1 int
    declare @ins_col2 int
    
    select * from inserted
    select * from deleted
    
    DECLARE trig_cursor CURSOR FOR 
    SELECT col1, col2 FROM INSERTED
    
    OPEN trig_cursor
    FETCH NEXT FROM trig_cursor INTO @ins_col1, @ins_col2
    
    while @@FETCH_STATUS = 0
    BEGIN
    	update test set col3 = getdate() 
    	where
    	col1 = @ins_col1 and col2 = @ins_col2
    
    	FETCH NEXT FROM trig_cursor INTO @ins_col1, @ins_col2	
    END
    
    go
    the error msg i got while execution ,

    Code:
    Server: Msg 16915, Level 16, State 1, Procedure trig_test, Line 13
    A cursor with the name 'trig_cursor' already exists.
    The statement has been terminated.

  7. #7
    Join Date
    Apr 2005
    Posts
    127
    Thanks Rajesh,

    That worked !!

    cheers
    Sn

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    first this should not be a cursor and is easily rewritten without one. second this code assumes you will only update this table one record at a time and it will fail to enforce your business rule in the case that an update statement is executed on this table that effects multiple rows.
    “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 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, for god's sake, don't use a cursor on this...
    Code:
    create trigger trig_test on test for update
    as
    
    update	test
    set	-- No need to set col1 or col2.  They are already set by the update statement!
    	--col1 = inserted.col1,
    	--col2 = inserted.col2,
    	col3 = getdate()
    from	test
    	inner join inserted on test.pkey = inserted.pkey
    go
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Excuse me, but why would you update the columns twice?

    Just do the update datetime column

    And if the access is through stored procedures only, then nlose the trigger and do it in the sproc

    I should write a book that has only 1 statement

    SPROC access to data ONLY
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm a firm believer in sprocs, as you know. But I use table triggers to maintain database auditing information, so such as the last time a record was modified and the last login to modify it.
    This data simply HAS to be kept at the table level, not the sproc level, because it is always possible to circumvent the sprocs. If not by a user, then by an admin. Plus, you may have several sprocs that update a table, so why repeat the same auditing code in each sproc?
    Sprocs are for the interface to use, but auditing data is there for the DBA and triggers fit the bill nicely.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, that's all I use them for as well, and it's good for security reasons as well, even if all data accesss is done by sprocs.

    With the audit, you can be assured that no other access is occuring, by let's say, a blind dba, or whatever

    http://weblogs.sqlteam.com/brettk/ar...0/20/2242.aspx
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Blind dbas are the only ones that can be trusted to view sensitive company data!
    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
  •