Trigger Help! - 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 Help!

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-12-29 : 11:08:43

The below is definitely not formed correctly just as an FYI so I need all the help I can get at a low-level explanation.

Create Trigger [dbo].[IT_Restrict_AreaCodes]
ON [dbo].[dialempty]
AFTER INSERT
AS

If
(select p.projecttype from project p INNER JOIN dialempty on p.ProjectID = inserted.ProjectID) = 2
AND
Left(inserted.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)
BEGIN
Update dialempty set phonenum = '1111111111'
END

What I need this to do is to watch any individual row inserts that happen on the dialempty table. Check the area code, if in whatever, then set the phone number to all ones. So far, I just an not sure on the following:

1) What kind of trigger I should be using, should it be instead of or is my after ok?
2) Syntax, something's not right, I get this error in Analyzer: The column prefix 'inserted' does not match with a table name or alias name used in the query.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-29 : 11:19:15

Try this:

Create Trigger [dbo].[IT_Restrict_AreaCodes]
ON [dbo].[dialempty]
AFTER INSERT
AS
BEGIN
update	dialempty
set	phonenum = '1111111111'
from	dialempty
	inner join inserted on dialempty.[PRIMARYKEY] = inserted.[PRIMARYKEY]
	inner join project on dialempty.ProjectID = project.ProjectID
where	project.projecttype = 2
	and Left(dialempty.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)
END

...and consider making your trigger fire on both insert and update.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources