SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 [Solved] Insert trigger scope
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ccbryan
Starting Member

4 Posts

Posted - 02/23/2010 :  09:21:05  Show Profile  Reply with Quote
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

Edited by - ccbryan on 02/23/2010 10:15:18

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/23/2010 :  09:24:37  Show Profile  Reply with Quote
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/

Go to Top of Page

ccbryan
Starting Member

4 Posts

Posted - 02/23/2010 :  10:14:48  Show Profile  Reply with Quote
Sweet! Thank you Visakh. And thanks for the UPDATE suggestion...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/23/2010 :  10:18:41  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/23/2010 :  10:29:55  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000