locked
problem with trigger RRS feed

  • Question

  • hello

    i have the next trigger:

    CREATE TRIGGER [app].[U_4_5_5] ON [app].[master]
    WITH EXECUTE AS CALLER
    FOR UPDATE
    AS
              BEGIN   
                    
                Declare @NewSeqVal int 
                          Declare @sql_txt varchar(2048)  
                      
     Declare @event_fire varchar(52)  
       Declare @my_id integer  
        Declare @curr_time1 VARCHAR(52)  
          Declare @i integer 
            set @my_id = 5;  
            if @i = 0  
              begin 
     update dbo.TIME_REGISTER set accessed = 1 where id=@my_id     
       set @curr_time1 = ( SELECT SYSDATETIME()) 
       update dbo.FIRE_TIME_UPDATE set time=@curr_time1  where id =1       
             end    else begin 
               update dbo.TIME_REGISTER set accessed = 1 where id=@my_id; 
     select time into curr_time1  from dbo.FIRE_TIME_UPDATE where ID=1  ;
         end  INSERT INTO dbo.SQLLog(language_event, parameters, event_info)      
              EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS;')      
        set @sql_txt = (select event_info from dbo.SQLLog)       
            delete from dbo.SQLLog       
                Exec @NewSeqVal = GetNewSeqVal_seqtable    
         INSERT INTO dbo.REPL_LOG (REPLICATION_NUMBER, SRC_DB, TGT_DB,  REPTYPE,KEY_FIELDS, STATUS, EVENT_TIME_STR, SQL_STMNT)  VALUES ( 4, 5,5, 'U', @NewSeqVal , '0' , @curr_time1 , @sql_txt )             INSERT INTO dbo.PRIMARY_KEY_FIELDS  VALUES  (0,2, @NewSeqVal , (SELECT ID1 from Deleted )) 
      INSERT INTO dbo.PRIMARY_KEY_FIELDS  VALUES  (0,1, @NewSeqVal , (SELECT ID1 from Inserted)) 
      INSERT INTO dbo.PRIMARY_KEY_FIELDS  VALUES  (1,2, @NewSeqVal , (SELECT ID2 from Deleted )) 
      INSERT INTO dbo.PRIMARY_KEY_FIELDS  VALUES  (1,1, @NewSeqVal , (SELECT ID2 from Inserted)) 
            
        set @i = (select min (accessed) from dbo.TIME_REGISTER)          
           if @i = 1              begin       
      update dbo.TIME_REGISTER set accessed=0   
      
     end         
                 END
    GO

    the first time it starts it is ok but after secind execution i tells that the object curr_time already exists in the database

    Thursday, November 11, 2010 10:51 PM

Answers

  • This line is causing the problem,

    select time into curr_time1 from dbo.FIRE_TIME_UPDATE where ID=1 ;
    

    What you are doing here is creating a new table in database by name "curr_time1",

    make sure you use "@curr_time1" to select in the variable,


    Vishal Gajjar MCITP\SQL08
    • Proposed as answer by PrinceLucifer Friday, November 12, 2010 6:36 AM
    • Marked as answer by Alex Feng (SQL) Friday, November 19, 2010 8:12 AM
    Friday, November 12, 2010 2:36 AM

All replies

  • Hi Ivan,

    Please use the "Insert Code Block" button to insert your code in a way
    that enables readers to understand this. With the code formatted as in
    this post, I can't make heads or tails of it.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 11, 2010 11:34 PM
  • This line is causing the problem,

    select time into curr_time1 from dbo.FIRE_TIME_UPDATE where ID=1 ;
    

    What you are doing here is creating a new table in database by name "curr_time1",

    make sure you use "@curr_time1" to select in the variable,


    Vishal Gajjar MCITP\SQL08
    • Proposed as answer by PrinceLucifer Friday, November 12, 2010 6:36 AM
    • Marked as answer by Alex Feng (SQL) Friday, November 19, 2010 8:12 AM
    Friday, November 12, 2010 2:36 AM
  • IF OBJECT_ID('curr_time1' ) IS NOT NULL

       DROP TABLE curr_time1

     

     I would create a table variabe or temporary table at the begining and just INSERT INTO.. do you use that technique because you do not know how many columns the table has???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 14, 2010 9:05 AM