CODEWITHSUNDEEP
sqlsql-server-2008triggers
Lali
Lali

Reputation: 2866

Query executes with no error but not in trigger

I am facing a strange problem in creating trigger in MS SQL. I have a query that executes with out an error but when I place it within trigger body, it gives error

Invalid column name 'ScreenName'.

I am placing whole trigger code here.

CREATE TRIGGER [dbo].[tr_tbFieldLabels_FieldLabelLength] ON [dbo].[tbFieldLabels]
AFTER INSERT
AS
    Update tbFieldLabels 
    Set TextBoxLength = (SELECT top 1 TextboxLength 
                              FROM  tbFieldLabelsSource FLS
                              WHERE FLS.ScreenName = Inserted.ScreenName
                              AND  FLS.SystemName = Inserted.SystemName)
     FROM tbFieldLabels , Inserted WHERE tbFieldLabels.ID = Inserted.ID
GO

Upvotes: 0

Views: 88

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107726

Your error does not correlate to the trigger code shown.

Given this structure:

create table tbFieldLabels (id int, screenname varchar(10), systemname varchar(10), textboxlength int);
create table tbFieldLabelsSource (screenname varchar(10), systemname varchar(10), textboxlength int);
insert tbFieldLabelsSource select 'abc', 'def', 99;
insert tbFieldLabelsSource select 'zyx', 'def', 67;
GO

The following trigger can be created successfully:

CREATE TRIGGER [dbo].[tr_tbFieldLabels_FieldLabelLength] ON [dbo].[tbFieldLabels]
AFTER INSERT
AS
    Update tbFieldLabels 
    Set TextBoxLength = (SELECT top 1 TextboxLength 
                              FROM  tbFieldLabelsSource FLS
                              WHERE FLS.ScreenName = Inserted.ScreenName
                              AND  FLS.SystemName = Inserted.SystemName)
     FROM tbFieldLabels , Inserted WHERE tbFieldLabels.ID = Inserted.ID
GO

Your code in the question would have failed with

The multi-part identifier "tbFieldLabelsSource.SystemName" could not be bound.

Because you have aliased tbFieldLabelsSource as FLS, but are still referencing it via the full name.

Using the trigger shown above, I can run this:

insert tbFieldLabels select 4, 'zyx', 'def', null;

Which is successful and inserts this record:

id          screenname systemname textboxlength
----------- ---------- ---------- -------------
4           zyx        def        67

Upvotes: 1

Related Questions