Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger

Author

Topic

rstout
Starting Member

25 Posts

Posted - 2005-03-30 : 00:47:50

I am new to SQL and need to make a trigger and have hacked a sample code to try to do what I want.

Here is what I need..
When A1 is updated or inserted on table ATT I need A1 to be copied to AL in the ATT table only if AL is empty or null.

SC, SN, and DY are the keys in the table named ATT

AL and A1 are valchar
SC,DY are smallint
SN is int

here is the new trigger
CREATE TRIGGER Copy_A1_to_AL_Trigger
ON ATT
INSTEAD OF update
AS

-- Have no clue what this does
IF @@rowcount = 0 RETURN

IF UPDATE (A1)
BEGIN
-- Just want the update or insert to go thru without any modification
IF i.AL != ''
BEGIN
-- Don't realy need it to print anything.
PRINT 'Do not update.'
RETURN
END

UPDATE t
SET AL = i.A1
FROM ATT t join inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
WHERE t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
END
RETURN

I get the following error in query anylzer
Server: Msg 107, Level 16, State 2, Procedure Copy_A1_to_AL_Trigger, Line 10
The column prefix 'i' does not match with a table name or alias name used in the query.

andy8979
Starting Member

36 Posts

Posted - 2005-03-30 : 06:01:46

The message you are getting is a syntax error you are using the alias name of i for the table.

the error is in the following code

IF i.AL != ''

check the condition entered.

:)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-03-30 : 06:20:21

you're using an alias i for which table?

you can't say "if i.al..." without referencing i to a table

you say, if (select al from inserted)<>'', but this only checks one record at a time, problem will arise if you insert more than 1 record in batch

--------------------
keeping it simple...

rstout
Starting Member

25 Posts

Posted - 2005-03-30 : 23:51:11

I was told to use the inserted deleted tables to do the referance to add the what was added to A1 to AL.

Is this even possible to do, I am still new to all this SQL stuff, I understand the logic but the syntax is what is killing me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources