#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9

    Insert Last Record Into Another Table Using Triggers


    We have a badge system we use to monitor activity in and out of a secured room. This information is recorded in table titled "EvnLog". I would like to use a trigger to take that data and insert it into another table that we will use to audit the activity as we cannot modify the exisiting table. Here is what I have come up with so far. However, it does not seem to be working as intended. The syntax checks out but I have yet to have it insert any data into the other table.

    PHP Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER 
    [dbo].[trig_EvnAudit]
       
    ON  [dbo].[EvnLog]
       
    AFTER INSERT
    AS 
    INSERT INTO EvnLogAudit SELECT FROM Inserted 
    Am I doing this correctly?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    What is table structure for EvnLogAudit table Is that exactly the same as EvnLog table and no additional columns? if yes it should work
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    No, they do not. I have the these columns in the EvnLog table:

    TimeDate (datetime, not null)
    Loc (int, not null)
    Event (smallint, not null)
    Dev (smallint, not null)
    IO (smallint, not null)
    IOName (nvarchar(30), not null)
    Code (bigint, not null)
    LName (nvarchar(30), not null)
    FName (nvarchar(30), not null)
    Opr (nvarchar(30), not null)
    Ws (nvarchar(30), not null)

    AND these columns in the EvnLogAudit table:

    TimeDate (datetime, not null)
    Loc (int, not null)
    Event (smallint, not null)
    Dev (smallint, not null)
    IO (smallint, not null)
    IOName (nvarchar(30), not null)
    Code (bigint, not null)
    LName (nvarchar(30), not null)
    FName (nvarchar(30), not null)
    Opr (nvarchar(30), not null)
    Ws (nvarchar(30), not null)
    srl_event (nvarchar(50), null)
    srl_event_details (nchar(10), null)
    srl_ticket_number (int, null)
    srl_vendors (nvarchar(50), null)
    srl_vendors_name (nvarchar(50), null)
    srl_last_update (datetime, null)
    srl_guest_vendors (nvarchar(50), null)
    srl_end_time (datetime, null)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    Originally Posted by gmrstudios
    No, they do not. I have the these columns in the EvnLog table:

    TimeDate (datetime, not null)
    Loc (int, not null)
    Event (smallint, not null)
    Dev (smallint, not null)
    IO (smallint, not null)
    IOName (nvarchar(30), not null)
    Code (bigint, not null)
    LName (nvarchar(30), not null)
    FName (nvarchar(30), not null)
    Opr (nvarchar(30), not null)
    Ws (nvarchar(30), not null)

    AND these columns in the EvnLogAudit table:

    TimeDate (datetime, not null)
    Loc (int, not null)
    Event (smallint, not null)
    Dev (smallint, not null)
    IO (smallint, not null)
    IOName (nvarchar(30), not null)
    Code (bigint, not null)
    LName (nvarchar(30), not null)
    FName (nvarchar(30), not null)
    Opr (nvarchar(30), not null)
    Ws (nvarchar(30), not null)
    srl_event (nvarchar(50), null)
    srl_event_details (nchar(10), null)
    srl_ticket_number (int, null)
    srl_vendors (nvarchar(50), null)
    srl_vendors_name (nvarchar(50), null)
    srl_last_update (datetime, null)
    srl_guest_vendors (nvarchar(50), null)
    srl_end_time (datetime, null)
    Code:
    create TRIGGER [dbo].[EvnLog_INsert] ON [dbo].[EvnLog]
    AFTER INSERT
    AS
    
    set nocount on
    
    
    
    insert into EvnLogAudit(TimeDate,Loc, Event, Dev, IO, IOName, Code, LName, FName, Opr, Ws)
    select
    	i.TimeDate,
    	i.Loc,
    	i.Event,
    	i.Dev,
    	i.IO,
    	i.IOName,
    	i.Code,
    	i.LName,
    	i.FName,
    	i.Opr,
    	i.Ws
    from inserted i
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    Originally Posted by gk53
    Code:
    create TRIGGER [dbo].[EvnLog_INsert] ON [dbo].[EvnLog]
    AFTER INSERT
    AS
    
    set nocount on
    
    
    
    insert into EvnLogAudit(TimeDate,Loc, Event, Dev, IO, IOName, Code, LName, FName, Opr, Ws)
    select
    	i.TimeDate,
    	i.Loc,
    	i.Event,
    	i.Dev,
    	i.IO,
    	i.IOName,
    	i.Code,
    	i.LName,
    	i.FName,
    	i.Opr,
    	i.Ws
    from inserted i
    Thank you. It worked like a charm. Would a failed trigger cause a database to lock up?

IMN logo majestic logo threadwatch logo seochat tools logo