Need help with a trigger

  • Hello, I need to create an insert trigger on a SQL Server 2005 database table that will set the inserted row date field to (CONVERT([varchar](10),getdate(),(112)). When I use the following code it seems to update all the rows in the table. I am used to being able to change the inserted value prior to the insert in Oracle. Here is what is not working for me:

    CREATE TRIGGER [dbo].[tr_intr_ob_problem_list_]

    ON [dbo].[ob_problem_list_]

    FOR INSERT

    AS

    BEGIN

    UPDATE ob_problem_list_

    SET datefield = (CONVERT([varchar](10),getdate(),(112)))

    FROM inserted i INNER JOIN ob_problem_list_ o ON i.person_id = o.person_id and i.seq_no = o.seq_no

    END

    Thank you,

    David

  • i believe it's becasue you want to update the alias "o",

    try this instead:

    CREATE TRIGGER [dbo].[tr_intr_ob_problem_list_]

    ON [dbo].[ob_problem_list_]

    FOR INSERT

    AS

    BEGIN

    UPDATE o --update the alias used below

    --stay with date times: simply trim the time portion off of the getdate:

    SET datefield = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

    FROM inserted i

    INNER JOIN ob_problem_list_ o

    ON i.person_id = o.person_id

    and i.seq_no = o.seq_no

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Or you could write it this way:

    UPDATE ob_problem_list_

    SET datefield = (CONVERT([varchar](10),getdate(),(112)))

    FROM

    inserted i

    WHERE

    i.person_id = ob_problem_list_.person_id and i.seq_no = ob_problem_list_.seq_no

    END

    Based on what it looks like you need to accomplich you'd probably be better off with a default constraint on the datefield column.

  • CELKO (7/18/2012)


    You are doing the wrongs things, the wrong way and have not given us enough information to help you. Want to try again?

    two syntactically correct , workable examples were posted before your reply...i thought there was enough information about this specific issue to identify teh problem(all rows updating) and the code with which to correct it.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply