Results 1 to 11 of 11

Thread: Trigger problem

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Trigger problem

    I have created a trigger that looks like this:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    ALTER TRIGGER dbo.TRG_ProgramLocking_JN
    ON dbo.ProgramLocking
    AFTER INSERT, UPDATE, DELETE
    AS

    DECLARE @ACT CHAR(1)
    DECLARE @INSERT INT
    DECLARE @DELETE INT

    SET @INSERT = 0
    SET @DELETE = 0


    IF @INSERT = 1 AND @DELETE = 0 SET @ACT = 'I'
    IF @INSERT = 1 AND @DELETE = 1 SET @ACT = 'U'
    IF @DELETE = 1 AND @INSERT = 0 SET @ACT = 'D'

    IF @ACT = 'I'
    BEGIN
    INSERT INTO ProgramLocking_JN
    (ProgramName, IsLocked, Operation, UpdateDate,
    UpdateUserid)
    SELECT ins.ProgramName, ins.IsLocked, 'I',
    ins.UpdateDate, ins.UpdateUserid
    FROM inserted ins
    END


    IF @ACT = 'D'
    BEGIN
    INSERT INTO ProgramLocking_JN
    (ProgramName, IsLocked, Operation, UpdateDate,
    UpdateUserid)
    SELECT del.ProgramName, del.IsLocked, 'D',
    del.UpdateDate, del.UpdateUserid
    FROM deleted del
    END


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    It compiled successfully, however, when I tested it, the journaling table didn't get populated. Any idea why?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    How about creating three seperate trigger. 1 for insert. 1 for update. 1 for delete. I do not see how this tells what kind of action is being performed. You have variables declared but I do not know what is populating them. As far as I can recall triggers rtake no arguements.
    “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.

  3. #3
    Join Date
    Aug 2004
    Posts
    99
    Declaring variables:

    Code:
    DECLARE @ACT CHAR(1)
    DECLARE @INSERT INT
    DECLARE @DELETE INT
    Asigning initial values to variables (except to @ACT):

    Code:
    SET @INSERT = 0
    SET @DELETE = 0
    None of the conditions in the next block are ever true since both @INSERT and @DELETE are 0, so there is no value assigned to @ACT

    Code:
    IF @INSERT = 1 AND @DELETE = 0 SET @ACT = 'I'
    IF @INSERT = 1 AND @DELETE = 1 SET @ACT = 'U'
    IF @DELETE = 1 AND @INSERT = 0 SET @ACT = 'D'
    Therefore your following If blocks are not executed since @ACT is neither 'I' nor 'D'

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Thrasymachus
    How about creating three seperate trigger. 1 for insert. 1 for update. 1 for delete. I do not see how this tells what kind of action is being performed. You have variables declared but I do not know what is populating them. As far as I can recall triggers rtake no arguements.
    Thrasymachus makes a very good point. Your variables do not get populated, therefor you have a 0 and 0 condition.

    Besides that, you should have a separate trigger for each action, since triggers are also optimized and placed into procedure cache upon the 1st execution. So even if you were able to populate the variables, your proc would not be optimized for 2 out of the three conditions.

  5. #5
    Join Date
    Jul 2004
    Posts
    268

    Post #27

    So what is the fix?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    typically i do not code things for other people but you need 3 triggers. here is the one for insert

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE TRIGGER dbo.TRG_ProgramLocking_JN
    ON dbo.ProgramLocking
    AFTER INSERT
    AS

    BEGIN
    INSERT INTO ProgramLocking_JN
    (ProgramName, IsLocked, Operation, UpdateDate,
    UpdateUserid)
    SELECT ins.ProgramName, ins.IsLocked, 'I',
    ins.UpdateDate, ins.UpdateUserid
    FROM inserted ins
    END


    Do 2 more. One for update and another for delete.
    “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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think you want to track history...so you would only want data from deleted...the current row in inserted will be in the base table

    Code:
    DROP TRIGGER dbo.TRG_ProgramLocking_JN
    GO
    
    CREATE  TRIGGER dbo.TRG_ProgramLocking_JN
    ON dbo.ProgramLocking
    FOR UPDATE, DELETE
    AS
    
    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    	INSERT INTO ProgramLocking_JN
    	(ProgramName, IsLocked, Operation, UpdateDate, 
    	UpdateUserid)
    	SELECT ins.ProgramName, ins.IsLocked, 'U',
    	ins.UpdateDate, ins.UpdateUserid
    	FROM deleted ins
    
    IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    	INSERT INTO ProgramLocking_JN
    	(ProgramName, IsLocked, Operation, UpdateDate, 
    	UpdateUserid)
    	SELECT del.ProgramName, del.IsLocked, 'D',
    	del.UpdateDate, del.UpdateUserid
    	FROM deleted del
    GO
    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.

  8. #8
    Join Date
    Jul 2004
    Posts
    268

    Post #4,767

    I need to keep a history on Insert as well as Update and Delete, that is a requirement.

    I have added an Insert part to the code you provided that looks like this:

    IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(select * FROM deleted)
    INSERT INTO ProgramLocking_JN
    (ProgramName, IsLocked, Operation, UpdateDate,
    UpdateUserid)
    SELECT ins.ProgramName, ins.IsLocked, 'I',
    ins.UpdateDate, ins.UpdateUserid
    FROM inserted ins


    It compiled successfully, but when I tested the insert into the base table, the journaling table has two records for insert instead of one. Why?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I question why you need the inserts...

    If you create a view between the journal and the base table, that would give you the same thing.

    Why retain redundant data?

    The "New" row is already accounted for...don't do this...

    Now why you have 2 rows, you'd have to help use out with DDL sample data, the newly rewritten trigger, and the test you performed....
    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.

  10. #10
    Join Date
    Jul 2004
    Posts
    268

    Post #4770

    I am not sure I understand your last sentence.

  11. #11
    Join Date
    Jul 2004
    Posts
    268
    Disregard my last reply. I figured it out. Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •