Simple trigger question

23 Aug 2006 11:28 PM

Rob

I want to create a trigger that keeps 2 columns on 1 table in synch....

Given the table..

CREATE TABLE [dbo].[tblTest] (
[KeyCol] [char] (10) COLLATE Latin1_General_BIN NULL ,
[Test1] [char] (10) COLLATE Latin1_General_BIN NULL ,
[Test2] [char] (10) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO

If a change is made to column Test1... then update column Test2 with the
value in Test1

Sql server will not allow me to even save the following trigger due to the
error below... what am I doing wrong ?
The error message is - Error 107: The column prefix "Inserted" does not
match with a table name or alias name use in the query.

CREATE TRIGGER trg ON [dbo].[tblTest]
FOR INSERT, UPDATE
AS
if Update(Test1)
begin
update tblTest Set Test2 = Test1 where tblTest.KeyCol = Inserted.KeyCol
end

Thanks !

23 Aug 2006 11:52 PM

Chris Lim

Try this:

CREATE TRIGGER trg ON [dbo].[tblTest]
FOR INSERT, UPDATE
AS
BEGIN
    IF UPDATE(Test1)
    BEGIN
        UPDATE tblTest
        SET Test2 = Test1
        FROM inserted i
        INNER JOIN tblTest t
             ON t.KeyCol = i.KeyCol
    END
END

24 Aug 2006 1:10 AM

Rob

Thanks Chris...
But now the error reads Error 209: Ambiguous column name "Test1"

Show quote

"Chris Lim" <blackca***@hotmail.com> wrote in message
news:1156377123.195247.166880@i42g2000cwa.googlegroups.com...
> Try this:
>
> CREATE TRIGGER trg ON [dbo].[tblTest]
> FOR INSERT, UPDATE
> AS
> BEGIN
>    IF UPDATE(Test1)
>    BEGIN
>        UPDATE tblTest
>        SET Test2 = Test1
>        FROM inserted i
>        INNER JOIN tblTest t
>             ON t.KeyCol = i.KeyCol
>    END
> END
>

24 Aug 2006 1:48 AM

Tom Cooper

Either change Chris's solution to:

CREATE TRIGGER trg ON [dbo].[tblTest]
FOR INSERT, UPDATE
AS
BEGIN
    IF UPDATE(Test1)
    BEGIN
        UPDATE tblTest
        SET Test2 = i.Test1
        FROM inserted i
        INNER JOIN tblTest t
             ON t.KeyCol = i.KeyCol
    END
END

Or use the following which uses the ANSI standard for UPDATE instead of the
SQL Server extentions

CREATE TRIGGER trg ON [dbo].[tblTest]
FOR INSERT, UPDATE
AS
If Update(Test1)
Begin
Update tblTest Set Test2 = (Select i.Test1 From inserted i Where
tblTest.KeyCol = i.KeyCol)
End

Tom

Show quote

"Rob" <rwch***@comcast.net> wrote in message
news:aoOdnadOT4hNZnHZnZ2dnUVZ_qOdnZ2d@comcast.com...
> Thanks Chris...
> But now the error reads Error 209: Ambiguous column name "Test1"
>
> "Chris Lim" <blackca***@hotmail.com> wrote in message
> news:1156377123.195247.166880@i42g2000cwa.googlegroups.com...
>> Try this:
>>
>> CREATE TRIGGER trg ON [dbo].[tblTest]
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>>    IF UPDATE(Test1)
>>    BEGIN
>>        UPDATE tblTest
>>        SET Test2 = Test1
>>        FROM inserted i
>>        INNER JOIN tblTest t
>>             ON t.KeyCol = i.KeyCol
>>    END
>> END
>>
>
>

24 Aug 2006 2:06 AM

Roy Harvey

On Wed, 23 Aug 2006 21:48:59 -0400, "Tom Cooper"
<tomcooper@comcast.no.spam.please.net> wrote:

>Or use the following which uses the ANSI standard for UPDATE instead of the
>SQL Server extentions
>
>CREATE TRIGGER trg ON [dbo].[tblTest]
>FOR INSERT, UPDATE
>AS
>If Update(Test1)
>Begin
> Update tblTest Set Test2 = (Select i.Test1 From inserted i Where
>tblTest.KeyCol = i.KeyCol)
>End

NOT good. This version will update every row that has no match in
INSERTED to NULL.

UPDATE tblTest
SET Test2 =
     (Select i.Test1 From inserted i
      Where tblTest.KeyCol = i.KeyCol)
WHERE EXISTS
     (Select * From inserted i2
      Where tblTest.KeyCol = i2.KeyCol)

Roy Harvey
Beacon Falls, CT

24 Aug 2006 2:52 AM

Tom Cooper

You're right. Thanks.

Tom

Show quote

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:272qe2dq92jutrg1j2qlbj3n40o5g8he69@4ax.com...
> On Wed, 23 Aug 2006 21:48:59 -0400, "Tom Cooper"
> <tomcooper@comcast.no.spam.please.net> wrote:
>
>>Or use the following which uses the ANSI standard for UPDATE instead of
>>the
>>SQL Server extentions
>>
>>CREATE TRIGGER trg ON [dbo].[tblTest]
>>FOR INSERT, UPDATE
>>AS
>>If Update(Test1)
>>Begin
>> Update tblTest Set Test2 = (Select i.Test1 From inserted i Where
>>tblTest.KeyCol = i.KeyCol)
>>End
>
> NOT good. This version will update every row that has no match in
> INSERTED to NULL.
>
> UPDATE tblTest
> SET Test2 =
>     (Select i.Test1 From inserted i
>      Where tblTest.KeyCol = i.KeyCol)
> WHERE EXISTS
>     (Select * From inserted i2
>      Where tblTest.KeyCol = i2.KeyCol)
>
> Roy Harvey
> Beacon Falls, CT

24 Aug 2006 3:01 AM

Chris Lim

Roy Harvey wrote:

> NOT good. This version will update every row that has no match in
> INSERTED to NULL.

Seeing as even experienced developers make this error, I think we can
notch up another benefit to using the non-ANSI version.... right Joe?
;-)