Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: create trigger help?

    I have a table that has a number of data fields,I need to be able to capture datatime when the date field was entered or entered value changed.I was told I need to create a trigger on that table that contains all the fields.

    I have seen the syntax for creating triggers, and read some documentation but I am still in the dark as how to create what I need to.

    I was hoping to see if somebody had a similar example or an advice, anything is more than what I have at the moment.


    CREATE TRIGGER NotifyDateFieldUpdates
    ON RelocateeRemovalist
    For INSERT, UPDATE, DELETE
    AS
    DECLARE @RemovalistNumber VARCHAR(200)
    DECLARE @RelocateID INT

    /*InspectionDate */
    DECLARE getInsp CURSOR FOR SELECT RelocateID,RemovalistNumber
    FROM INSERTED a LEFT JOIN DELETED b ON (a.RemovalistNumber=b.RemovalistNumber and a.RelocateID=b.RelocateID)
    WHERE a.InspectionDate IS NOT NULL AND b.InspectionDate IS NULL

    OPEN getInsp

    FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    INSERT INTO RelocateeRemovalistFieldEntry(RElocateID, RemovalistID)SELECT
    RelocateID,RemovalistID FROM INSERTED a LEFT JOIN RelocateeRemovalistFieldEntry b ON
    (a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)WHERE b.RElocateID is null


    UPDATE RelocateeRemovalistFieldEntry SET InspectionDateDateTime=GETDATE()
    WHERE RelocateID=@RelocateID aND RemovalistNumber=@RemovalistNumber

    FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
    END

    DEALLOCATE getInsp

    GO

    This is what I was able to come up with so far,but when i check the syntax it gives me an error "Ambiguous column name "RelocateID" and "Ambiguous column name "RemovalistNumber" I don't know what is it trying to tell me here and couldn't find much help.

    Regards and thanks
    Last edited by zobernjik; 09-15-04 at 00:24.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah the cursor has ambiguous colums...just add a. to them...

    BUT you NEED to lose the CURSOR...

    What's the business logic?
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Sorry but what exactly do you mean by "I need to lose the cursor", do you CLOSE the cursor, I have done that now.

    another thing, if I check the syntax it is ok, but if I try to save it gives me an error "Error 21001:[Sql-DMO]Stored procedure definition must include name and text (For Standard StoredProcedure) or libraryname (for Extended StoredProcedure), I can't find much about this error......!!!

    The business purpose is that we can show our clients when certain dates that make us act and do business were entered as opposed to when the business was done or stared,etc....

    Pain in the ...

    Thanks
    Last edited by zobernjik; 09-15-04 at 21:34.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No I mean get rid of the CURSOR....

    This should all be able to be done via set processing

    My Own Opinion (MOO) you should NEVER use a cursor in a TRIGGER
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thanks for your reply again.

    I would be happy to lose the cursor except that I don't know how to go about it in any other way, even though I can see this is not a good way, all the examples that i can find are completely different from what I need and therefore not helpfull.

    A number of people have viewed the post but nobody seems have done something similar or are not willing to share their example (fair enough).

    So, after all this, still didn't get that far.

    Thanks mate

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Brett will love me for jumping in, but he's gone for three days so...
    Code:
    CREATE TRIGGER NotifyDateFieldUpdates 
    ON RelocateeRemovalist
    FOR INSERT, UPDATE, DELETE
    AS
    
    INSERT INTO RelocateeRemovalistFieldEntry (
       RelocateID, RemovalistID, InspectionDateTime
    )  SELECT
       a.RelocateID, a.RemovalListID, GetDate()
       FROM inserted AS a
       WHERE  NOT EXISTS (SELECT *
          FROM RelocateeRemovalistFieldEntry AS b
          WHERE  b.RelocateID = a.RelocateID
             AND b.RemovalistNumber = a.RemovalistNumber)
    
    RETURN
    GO
    -PatP

  7. #7
    Join Date
    Apr 2004
    Posts
    101
    Try this one.

    CREATE TRIGGER [Trg1] ON dbo.Tbl1
    FOR INSERT, UPDATE
    AS
    BEGIN
    UPDATE Tbl1 SET update_dtm = (SELECT GETDATE())
    WHERE Tbl1.id IN (SELECT id FROM inserted)
    END

    Note: Assuming id is your primarykey.

  8. #8
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thank you to everybody who helped me to get to this stage and I have tested the syntax Pat Phelan has sent me and it works like a dream,but there is but, I need alter this trigger and be able to update about 8 fields, it doesn't mean that all of them will get updated all the time,but there has to be a possilbility to do so.


    What I have done to the syntax posted by Pat P. is added another block of code and changed the date field I am updating, on the "check syntax" that seems to be correct but when I go and update the field and go back to look at the result of the update the actula field that should have been updated is null, but there is another field that is getting updated in stead, in fact no matter which field I updated it is only one field in the log table that gets updated.

    What am I doing wrong??

    Thanks and regards

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Would you rather post your code, or have us guess?

    -PatP

Posting Permissions

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