CODEWITHSUNDEEP
sql-server-2008triggers
user1703145
user1703145

Reputation: 175

SQL Server trigger insert and update operation not working?

I am using SQL Server 2008. My trigger shown below. And I am inserting the values I didn't get any update in two tables .

 CREATE TRIGGER trgAfterInserta ON AMS_33.dbo.access_event_logs
 AFTER INSERT,UPDATE 
 AS           
        declare @idams bigint;  
        declare @id int; 
        declare @uid nvarchar(20);
        declare @tentry datetime;
        declare @tsn nvarchar(20);
        declare @evtid nvarchar(20);
        declare @audit_action nvarchar(300);

select @idams   = i.id from inserted i;
select @id      =  max(Rownumber) + 1   from   TAV3.dbo.access_event_logs ;  
select @uid    =i.USERID from inserted i;   
select @tentry =i.TIMESTAMPS from inserted i;   
select @tsn   =i.TERMINALSN from inserted i;
select @evtid =i.EVENTID from inserted i;
set    @audit_action='Inserted Record -- After Insert.';
set    @tentry = dateadd(hh,3,@tentry); 


insert into TAV3.dbo.access_event_logs 
(Rownumber,Userid, Timeentry, Eventid, Terminalsn, Notes)
 values(@id,@uid, @tentry, @evtid, @tsn, @audit_action);

update AMS_33.dbo.access_event_logs set Calculated=1 where id= @idams;

GO

Table 1 AMS_33.dbo.access_event_logs

  id    bigint  Unchecked
  USERID    nvarchar(50)    Unchecked
  DEPARTMENT    nvarchar(50)    Unchecked
  TIMESTAMPS    datetime    Unchecked
  EVENTID   nvarchar(50)    Unchecked
  TERMINALSN    nvarchar(50)    Unchecked
  ACCESSMETHOD  nvarchar(255)   Unchecked
  REMARKS   nvarchar(255)   Unchecked
  TERMINALIP    nvarchar(50)    Unchecked
  PHOTO image   Checked
  PHOTOSIZE int Unchecked
  RECLOGDTM datetime    Unchecked
  RECLOGFROMIP  nvarchar(50)    Unchecked
  LOCALTIMESTAMP    datetime    Unchecked
  PhotoPath nvarchar(50)    Checked
  Calculated    int Checked

Table 2 TAV3.dbo.access_event_logs 

Rownumber   int Unchecked
Userid  nvarchar(20)    Unchecked
Timeentry   datetime    Unchecked
Eventid nvarchar(20)    Checked
Terminalsn  nvarchar(20)    Unchecked
Calculated  int Checked
Notes   nvarchar(300)   Checked

Here no insert or update is performing ?

Upvotes: 1

Views: 326

Answers (1)

automatic
automatic

Reputation: 2737

You are assuming only one row has been inserted or updated, in statements like select @idams = i.id from inserted i;

That could be false. Those statement would then fail never getting to the insert

Upvotes: 1

Related Questions