insert/update trigger fails on error 107

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

SQL Server Development (2000)

insert/update trigger fails on error 107

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-07-23 : 11:57:04

Hi. I'm trying to create a trigger that will update RACECODE column in my table based on the value entered by the user in ETHNICID column.

First problem: When I check syntax for the code below, I get "error 107: the column prefix background does not match with a table name or alias used in the query".

Second problem: can someone show me how to capture UPDATES as well as fresh inserts? I know "FOR INSERT, UPDATE" but need assist with WHERE clause at end to include updates as well.

Here's the code:

CREATE TRIGGER trInsUpdbackground_RaceCode

ON dbo.background

FOR INSERT AS

IF background.EthnicID = 16

BEGIN

UPDATE background
SET background.RaceCode = 1

FROM inserted
WHERE background.PersonID = inserted.PersonID

END

many thanks...

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-23 : 12:27:29

quote:

First problem: When I check syntax for the code below, I get "error 107: the column prefix background does not match with a table name or alias used in the query".

 IF dbo.background.EthnicID = 16

quote:

Second problem: can someone show me how to capture UPDATES as well as fresh inserts? I know "FOR INSERT, UPDATE" but need assist with WHERE clause at end to include updates as well.

Instead of


IF background.EthnicID = 16   
BEGIN
       UPDATE background
       SET background.RaceCode = 1  
       FROM inserted
       WHERE background.PersonID = inserted.PersonID
END

Try :


CREATE TRIGGER
.....

AS

UPDATE dbo.background
SET dbo.background.RaceCode = 1
FROM inserted AS i
INNER JOIN dbo.background AS b
ON i.PersonID = b.PersonID
WHERE dbo.background.EthnicID = 16

END

---------------
Shadow to Light

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 12:33:46



CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE 
AS
-- An update
If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

-- A Delete
If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

-- An Insert
If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
If Exists (Select 1 From Inserted) And NOT Exists (Select 1 From Deleted)


And this needs to be (I think)

IF background.EthnicID = 16 


Becomes 

IF INSERTED.EthnicID = 16

Brett

8-)

Edited by - x002548 on 07/23/2003 13:23:12

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-07-23 : 12:36:42

thanks for the tip. I should have mentioned that I used the conditional phrasing b/c there will be 20 possibilities for ETHNICID. These 20 will then correspond to 6 general categories that will be auto-written to RACECODE based on ETHNICID.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-07-23 : 12:50:04

now that I consider the potential headaches associated with "triggering" this, I think I'll just javascript it in on the web form via hidden field and bypass the trigger altogether. thanks for the assists.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources