[Solved] Insert trigger scope - 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

General SQL Server Forums

New to SQL Server Programming

[Solved] Insert trigger scope

Author

Topic

ccbryan
Starting Member

4 Posts

Posted - 2010-02-23 : 09:21:05

Hi, quick question here... I am uploading data from retail outlets to the corporate office, and I want to ensure that credit card data is not recorded at corporate. I'm thinking an INSERT trigger like this one would be a good way to handle this:

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[payments]
FOR INSERT
AS
update dbo.payments set cardnumber = '', cardname = '', carexpire = ''

However, I want to make sure the update statement applies only to the record being inserted. Will that be true the way this trigger is written? If not, how should it look?

Thanks,

Chandler

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 09:24:37

for that you need to do join with inserted


CREATE TRIGGER [TRIGGER NAME] ON [dbo].[payments] 
FOR INSERT
AS
update p 
set p.cardnumber = '', p.cardname = '', p.carexpire = '' 
from dbo.payments p
join inserted i
on i.pk=p.pk

pk is primary key of table

b/w dont you need to have trigger for update as well? just in case, somebody changes an already existing value of table in [dbo].[payments]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbryan
Starting Member

4 Posts

Posted - 2010-02-23 : 10:14:48

Sweet! Thank you Visakh. And thanks for the UPDATE suggestion...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:18:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 10:29:55

Couple of suggestions:

Assuming you are bulk uploading the data to corporate then the Trigger will create additional log entries (could be significant if volumes are large).

You might want to have the Bulk Upload set these fields to blank, and then have the trigger conditionally update only if any of these columns are NOT blank - that way it won't re-update rows that don't need it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources