Update Trigger

23 Jun 2006 12:53 PM

Paul Cheetham

Hi,

I'm new to writing triggers, and I can't seem to get the syntax right of
this one. What I am trying to do is this.

On Update of DB1.Table1
   If (Update(Table1.Completed))
     UPDATE DB2.Table2
     SET DB2.Table2.Completed = DB1.Table1.Completed
     WHERE DB2.Table2.JobNumber = DB1.Table1.JobNumber

The code for my trigger at the moment is this:

CREATE TRIGGER [Update_Completed] ON [dbo].[Table1]

FOR UPDATE
AS

IF UPDATE ([Completed])
BEGIN
    UPDATE DB2.dbo.[Table2]
    SET
        [Completed] = inserted.[Completed]
    WHERE
        DB2.dbo.[Table2].WorksNumber= inserted.[WorksNumber]

END

I am getting an error: 'The column prefix 'inserted' does not match with
a table name or alias name used in the query'

Now I know what this means, but all the information I can find tells me
that 'inserted' is a conceptual table that is available in Insert and
Update triggers.

Can anyone tell me where I am going wrong?

I am using SQL Server 2000

Thankyou.

Paul

23 Jun 2006 1:05 PM

Tracy McKibben

Paul Cheetham wrote:
Show quote

> Hi,
>
> I'm new to writing triggers, and I can't seem to get the syntax right of
> this one. What I am trying to do is this.
>
> On Update of DB1.Table1
>   If (Update(Table1.Completed))
>     UPDATE DB2.Table2
>     SET DB2.Table2.Completed = DB1.Table1.Completed
>     WHERE DB2.Table2.JobNumber = DB1.Table1.JobNumber
>
>
> The code for my trigger at the moment is this:
>
>
> CREATE TRIGGER [Update_Completed] ON [dbo].[Table1]
>
> FOR UPDATE
> AS
>
>
> IF UPDATE ([Completed])
> BEGIN
>     UPDATE DB2.dbo.[Table2]
>     SET
>         [Completed] = inserted.[Completed]
>     WHERE
>         DB2.dbo.[Table2].WorksNumber= inserted.[WorksNumber]
>
> END
>
>
>
> I am getting an error: 'The column prefix 'inserted' does not match with
> a table name or alias name used in the query'
>
> Now I know what this means, but all the information I can find tells me
> that 'inserted' is a conceptual table that is available in Insert and
> Update triggers.
>
> Can anyone tell me where I am going wrong?
>
> I am using SQL Server 2000
>
> Thankyou.
>
>
> Paul

That's not the proper way to join two tables. Your update statement
needs to be:

UPDATE DB2.dbo.Table2
SET Completed = inserted.Completed
FROM DB2.dbo.Table2
INNER JOIN inserted
ON Table2.WorksNumber = inserted.WorksNumber

23 Jun 2006 1:26 PM

Paul Cheetham

OK Thanks for that - I'll give it a go.

Tracy McKibben wrote:
Show quote

> Paul Cheetham wrote:
>> Hi,
>>
>> I'm new to writing triggers, and I can't seem to get the syntax right
>> of this one. What I am trying to do is this.
>>
>> On Update of DB1.Table1
>>   If (Update(Table1.Completed))
>>     UPDATE DB2.Table2
>>     SET DB2.Table2.Completed = DB1.Table1.Completed
>>     WHERE DB2.Table2.JobNumber = DB1.Table1.JobNumber
>>
>>
>> The code for my trigger at the moment is this:
>>
>>
>> CREATE TRIGGER [Update_Completed] ON [dbo].[Table1]
>>
>> FOR UPDATE
>> AS
>>
>>
>> IF UPDATE ([Completed])
>> BEGIN
>>     UPDATE DB2.dbo.[Table2]
>>     SET
>>         [Completed] = inserted.[Completed]
>>     WHERE
>>         DB2.dbo.[Table2].WorksNumber= inserted.[WorksNumber]
>>
>> END
>>
>>
>>
>> I am getting an error: 'The column prefix 'inserted' does not match
>> with a table name or alias name used in the query'
>>
>> Now I know what this means, but all the information I can find tells
>> me that 'inserted' is a conceptual table that is available in Insert
>> and Update triggers.
>>
>> Can anyone tell me where I am going wrong?
>>
>> I am using SQL Server 2000
>>
>> Thankyou.
>>
>>
>> Paul
>
> That's not the proper way to join two tables. Your update statement
> needs to be:
>
> UPDATE DB2.dbo.Table2
> SET Completed = inserted.Completed
> FROM DB2.dbo.Table2
> INNER JOIN inserted
>    ON Table2.WorksNumber = inserted.WorksNumber