Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Posts
    56

    Unanswered: SQL Trigger to run on insert only

    Rightio - this is probably a simple question for you SQL afficionados.

    I have written a trigger to update a master table with a CreateDate field. Simple enough I thought but it updates this field when the existing record is edited as well - not so good.

    This is how it looks like:

    CREATE TRIGGER CreateDate
    ON Master
    FOR UPDATE AS

    DECLARE
    @idMaster int

    SELECT @idMaster = idMaster from Inserted

    update Master
    set CreatedDate = getdate()
    where idMaster = @idMaster

    GO

    Well I know I can write an IF statement that will basically say if the field is not null then only update - fair enough - but is there a simpler way to do this? Is there a way I need to write my CREATE TRIGGER statement that ONLY makes it run when it is a NEW INSERT ONLY?

    THANKS!
    Puppies and cheesecake.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TRIGGER CreateDate_Insert
      ON master
    FOR INSERT
    AS
    ...
    However, have you thought of simply applying a default value to the field?
    This value will be assigned when the record is created.
    Code:
    ALTER TABLE tableName
    ADD exampleColumn int DEFAULT (400)
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    It is indeed a very simple question with an equally simple solution. The part of the trigger definition that you need to pay attention to is the FOR ... clause. It is here that you declare under which DML events the trigger will be invoked.

    Your current definition declares the trigger to execute only in response to an UPDATE event. To change it to fire on INSERT events, just change the word UPDATE to INSERT.

    So,

    Code:
    FOR UPDATE AS
    becomes

    Code:
    FOR INSERT AS
    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Had I not written that brief explanation, I would have posted at 18:17 or so, two minutes before my actual post at 18:19, which was the time when you posted yours.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh and interestingly a very similar topic appeared this morning...
    http://www.dbforums.com/showthread.php?t=1624416
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Posts
    56
    Hmmm.... strange. I tried the FOR INSERT AS option while I was playing around and it didn't update my new fields at all. ??? *mumbles* - I wonder what I did wrong. Will try again.

    Oh, and just to clear - I'm interacting with an existing program that only allows us to create additional user-defined fields in the front-end that users can enter data into. I'm trying a hack to insert the create date automatically using a SQL trigger cause the user wants to do reporting based on this.

    Thanks guys - I always appreciate the help and suggestions.
    Puppies and cheesecake.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by Mynx
    Oh, and just to clear - I'm interacting with an existing program that only allows us to create additional user-defined fields in the front-end that users can enter data into. I'm trying a hack to insert the create date automatically using a SQL trigger cause the user wants to do reporting based on this.
    This is one such example of the kind of unfortunate problems that can occur, and which often result in catastrophic consequences for an organisation, which can include bankruptcy due to loss or corrupt data, when application developers are allowed anywhere near a company's data or allowed to offer any degree of input to the design of an data system. In application developer I refer to anyone who considers their many language to be any of the JAVA, .NET, C#, VB, PHP, Cold Fusion, all of which are used in the most trivial of manners in development projects today.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This should not be done with a trigger, instead should be with a default value constraint.
    If you insist on using the trigger for this then post the code you've been trying and we might be able to spot what's amiss.
    George
    Home | Blog

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    If you're using transaction APIs, which I certainly hope you are, then this type of problem will never occur. As georgev suggested, the use of a default constraint is the correct way to enforce this kind of rule.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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