Solved

MS SQL 2008R2, run only run trigger for new records.

Posted on 2013-01-20
3
496 Views
Last Modified: 2013-01-22
I have a trigger to update and fix wrong data like this:

create trigger FixDataMC1 on MC1LINE2
after update, insert, delete
as
update MC1LINE2 set [Date] = REPLACE([Date], '"', ''), [Time] = REPLACE([Time], '"', ''), [VACUUM_PRESS.] = REPLACE([VACUUM_PRESS.], ',', '')

update MC1LINE2 set [Date] = '20' + [Date] where LEN([Date]) <=8

delete from MC1LINE2 where [No.] like '%No.%'
go

Open in new window


And here is bulk insert from csv files:

BULK INSERT vw_bulk_insert_test
FROM '\\172.27.10.11\MC1-LINE #2\MC01-2013-0120-0448.csv'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
FIRE_TRIGGERS

)

Open in new window


My problem is: csv file contains a lot of data. Daily, I have to import a lot of data from csv file into SQL, the trigger needs to update wrong data for me, but I just want the trigger fix data for just imported data (new records only), not for already updated data, currently, I have to wait long time to run the trigger for all my existing records.

How can I do that?

Thanks!
0
Comment
Question by:JameMeck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 100 total points
ID: 38801409
First you might want to have a read of the BOL documentation on creating a trigger and use of the inserted and deleted tables:

http://msdn.microsoft.com/en-us/library/ms191300%28v=sql.105%29.aspx
http://msdn.microsoft.com/en-us/library/ms189799%28v=sql.105%29.aspx

Then something like this for after insert or update (since after a delete you really don't need to both with updating the record you just deleted)

create trigger FixDataMC1 on MC1LINE2
after update, insert
as
begin
update MC1LINE2 
set [m.Date] = REPLACE([m/Date], '"', ''), 
    [m.Time] = REPLACE([m.Time], '"', ''), 
    [m.VACUUM_PRESS.] = REPLACE([m.VACUUM_PRESS.], ',', '')
from MC1LINE2 m
inner join inserted i
on (m.key_column1= i.key_column1
and m.key_column2 = i.key_column2)  -- or just one key column if that's all you have

update MC1LINE2 
set [mDate] = '20' + [mDate] 
from MC1LINE2 m
inner join inserted i
on (m.key_column1= i.key_column1
and m.key_column2 = i.key_column2)  -- or just one key column if that's all you have
where LEN([Date]) <=8
end
go

Open in new window


I haven't tested the above, it's modified code from an existing trigger I have on one of my servers where an update on one table causes an update to a second table.

Oracle has a construct of a mutating table, where you can't use a trigger to modify a row in a table that caused the trigger to fire, I'm not sure if SQL Server has the same construct.

Another thought - wouldn't it be easier to pre-clean the data before loading it ?  I am reminded of a concept known as GIGO - Garbage In, Garbage Out.

Your database is only as good as what you feed it.  Feed it garbage, it will give you garbage when you need to fetch the data.

Can you modify your input process so that it pre-screens the data so that by the time you load it, you know it's clean ?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 38802063
Removed the "AFTER DELETE" as it is not needed.

Moved the "DELETE" in the trigger to the front of code: there's not point in UPDATEing rows in the trigger only to immediately DELETE them.

Finally, I added the schema name to the objects in the trigger.  Trigger objects should always be fully qualified whenever possible, to make sure that the wrong object is not UPDATEd by accident!  It's also more efficient, since SQL doesn't have to re-resolve the table schema at run time every time the trigger executes.



CREATE TRIGGER dbo.FixDataMC1
ON dbo.MC1LINE2
AFTER UPDATE, INSERT
AS

DELETE FROM mc1
FROM dbo.MC1LINE2 mc1
INNER JOIN inserted i ON
    i.key_col = mc1.key_col --AND i.key_col_2 = mc1.key_col_2 AND ...
WHERE
    [No.] LIKE '%No.%'

UPDATE mc1
SET
    [Date] = REPLACE(mc1.[Date], '"', ''),
    [Time] = REPLACE(mc1.[Time], '"', ''),
    [VACUUM_PRESS.] = REPLACE(mc1.[VACUUM_PRESS.], ',', '')
FROM dbo.MC1LINE2 mc1
INNER JOIN inserted i ON
    i.key_col = mc1.key_col --AND i.key_col_2 = mc1.key_col_2 AND ...

UPDATE mc1
SET
    [Date] = '20' + [Date]
FROM dbo.MC1LINE2 mc1
INNER JOIN inserted i ON
    i.key_col = mc1.key_col
WHERE
    LEN([Date]) <= 8

GO
0
 

Author Closing Comment

by:JameMeck
ID: 38808668
Thanks!

I am using it, it is running well.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question