Trigger Help!

Question

Post reply

Trigger Help!

dba321

Hall of Fame

Points: 3917
More actions
December 29, 2005 at 9:30 am

#90273

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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

the insert trigger could be something like:

Create Trigger [dbo].[IT_Restrict_AreaCodes]

ON [dbo].[dialempty]

AFTER INSERT

AS

BEGIN

if @@rowcount = 0 return

Update d set phonenum = '1111111111'

From dialempty d join inserted i on d.ProjectID = i.ProjectID

Where Left (i.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)

END

I don't know what is the relationship of the project table with all of these??

Should you need to include the project table, please post the DDL for the tables involved.

* Noel

dba321 Hall of Fame Points: 3917 More actions · Answer 2

yea, there needs to be an inner join between table Project and table dialempty based on ProjectID. Maybe you're doing it correctly based on the inserted ProjectID which is really what I want.

dba321 Hall of Fame Points: 3917 More actions · Answer 3

it works fine. Thanks for the additional syntax change help.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

We need to know the PRIMARY KEY or the UNIQUE row identifiers of the DialEmpty table.

For a trigger like this, you need a correlated UPDATE between inserted and DialEmpty and it is not possible to create the correlation if we don't know which column or columns uniquely identify each row.

The general form of the UPDATE will be:

UPDATE d

Set PhoneNum = '1111111111'

FROM DialEmpty As d

INNER JOIN inserted as i

On ( {need correlating column or columns here} )

INNER JOIN Project As p

On (p.ProjectID = i.ProjectID)

WHERE p.ProjectType = 2

AND Left(i.PhoneNum,3)

-- Wouldn't these hard-coded numbers be better in a table ?

-- If this set changes, it's a data change, not a hunt & peck for all SQL code

-- containing this IN() string ..

IN ('203','475','860','959','406','218','320','507','612','651','763','952')

dba321 Hall of Fame Points: 3917 More actions · Answer 5

Mr. or Ms. 500, noeld's worked great by inner joining the inserted

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

>>worked great by inner joining the inserted

It works, sure, but if ProjectID is not the unique identifier of DialEmpty, then that UPDATE will also hit rows that were not recently inserted. This in turn will become a performance issue as the DialEmpty table grows.

Unless you join DialEmpty to inserted on the primary key of DialEmpty, you are setting yourself up for future performance issues.

dba321 Hall of Fame Points: 3917 More actions · Answer 7

>>>It works, sure, but if ProjectID is not the unique identifier of DialEmpty, then that UPDATE will also hit rows that were not recently inserted. This in turn will become a performance issue as the DialEmpty table grows.

I'm not quite getting what you are saying. Why would it check all other rows?

dba321 Hall of Fame Points: 3917 More actions · Answer 8

so you're saying something like this

on inserted.dialemptyID = d.dialemptyId

if so, then can you explain how this is all interecting with the inserted table?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 9

>>so you're saying something like this

>>on inserted.dialemptyID = d.dialemptyId

If DialEmptyID is the unique/primary key, then yes, that's exactly what's needed.

The 'inserted' table is just a virtual table that is treated like any other table by T-SQL. There is no inherent link between it and the table being inserted to, so if you choose to join it on a non-primary key that affects rows that are already in the table, then you end up updating rows that weren't in the set just inserted.

eg: Say your DialEmpty table has 100K rows, 10K of which are in ProjectID 123. If you insert just 1 new record containing a ProjectID of 123, and you mistakenly join on ProjectID instead of DialEmptyID, your UPDATE will hit 10,001 records, the 10,000 that were already in your table, plus the new 1 just added.

dba321 Hall of Fame Points: 3917 More actions · Answer 10

set

ANSI_NULLS ON

set

QUOTED_IDENTIFIER ON

go

ALTER

Trigger [IT_Restrict_AreaCodes]

ON

[dbo].[DialEmpty]

AFTER

INSERT

AS

if

@@rowcount = 0 return

Update

d set phonenum = '1111111111'

From dialempty d join inserted i on d.ProjectID = i.ProjectID

inner join project p on p.projectid = i.projectid

inner join inserted ON i.DialID = d.DialID

Where

Left (i.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)

AND

p.projecttype = 2

dba321 Hall of Fame Points: 3917 More actions · Answer 11

Thanks a lot for the explanation Mr. or Ms. 500, appreciated. I just posted my latest code which hopefully should cover that issue now that you addressed.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 12

You are joining to the 'inserted' table twice, and you are still hitting records not in the inserted set. Also, you are taking the LEFT() of a string and then comparing it to a list of integers - why force SQL server to do a un-necessary type conversion ?