Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    10

    Unanswered: Insert Trigger Not Working.. Please help!!!

    Hi,

    I have written the below insert trigger. Whenever there is an insert in Table1, the row needs to be inserted into Table2.

    CREATE TRIGGER trigger_A ON Table1 FOR INSERT AS

    insert into Table2( NewEmployeeNo,OldEmployeeNo,MergedDate, DeletedDate, CreatedBy, CreatedDate,ModifiedBy,ModifiedDate)
    select
    (select employeenumber from EMP where id = inserted.NewE_Id),
    (select employeenumber from EMP where id = inserted.OldE_Id),
    MergedDate, DeletedDate,
    (select loginname from USER where id = inserted.CreatedById),
    CreatedDate,
    (select loginname from USER where id = inserted.ModifiedById),
    ModifiedDate from inserted

    The trigger is not inserting MergedDate,DeletedDate,ModifiedDate columns. But it does insert all the other(NewEmployeeNo,OldEmployeeNo,CreatedBy, CreatedDate,ModifiedBy) columns.

    Can you please help me to resolve this issue ? Thanks in Advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No offense, but you need to brush up on your SQL before diving into writing triggers. A poorly written trigger is one of the worst things to have in a database.
    Here is a better way to write your code, without all those nested select statements:
    Code:
    CREATE TRIGGER trigger_A ON Table1 FOR INSERT AS
    
    insert into Table2
    	(NewEmployeeNo,
    	OldEmployeeNo,
    	MergedDate, 
    	DeletedDate, 
    	CreatedBy, 
    	CreatedDate,
    	ModifiedBy,
    	ModifiedDate)
    select	NewEMP.employeenumber as NewEmployeeNo,
    	OldEMP.employeenumber as OldEmployeeNo,
    	inserted.MergedDate,
    	inserted.DeletedDate,
    	CreatedByUSER.loginname as CreatedBy,
    	inserted.CreatedDate,
    	ModifiedByUSER.loginname as ModifiedBy
    	inserted.ModifiedDate
    from	inserted
    	left outer join EMP as NewEMP on inserted.NewE_Id = NewEMP.ID
    	left outer join EMP as OldEMP on inserted.OldE_Id = OldEMP.ID
    	left outer join USER as CreatedByUSER on inserted.CreatedByID = CreatedByUSER.id
    	left outer join USER as ModifiedByUSER on inserted.ModifiedByID = ModifiedByUSER.id
    If that doesn't work for you, then post back again.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Also, have you 100% guaranteed that there are, in fact, values for those three fields in the data you are inserting?

    Maybe the reason they are not being inserted is that they don't exist!

    You need to absolutely, 100%, check in two or three different ways, and guarantee that those values even exist.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Mar 2012
    Posts
    10
    Hi Blindman , thanks for the code.
    I'm totally new to SQL server and i'm in the situation to write the triggers now

    Hi ken, you are correct. Those columns were not populated during insert.

    Mergeddate and deleteddate columns were not populated because they were initially inserted as NULL and later on those columns are updated with values. So in order to have these columns populated, i need to write Update trigger.
    Can you please help me on this ?

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    gbala,

    I don't believe in triggers--I believe they are the GOTO statements of SQL.

    Whatever is doing the insert into table1 should also do the insert into table2, without a trigger.

    Ken
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Mar 2012
    Posts
    10
    A batch program is inserting data into Table1 and we are extracting few of the columns from this table whenever there is an insert or update.

    we can not use the batch program to insert into Table2 as the column values are not direct values ex - newemployeeno,oldemployeeno (Please refer the insert script).

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by PracticalProgram View Post
    gbala,

    I don't believe in triggers--I believe they are the GOTO statements of SQL.

    Whatever is doing the insert into table1 should also do the insert into table2, without a trigger.

    Ken
    They are in no way equivalent to GOTO statements. At all. Nada.
    They are, in fact, quite the opposite. They keep data logic as close to the data as possible. The solution of reimplementing identical logic in every insert statement that accesses a table is actually more akin to the "spaghetti code" of old GOTO statements.
    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
  •