?
Solved

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

Posted on 2013-01-20
3
Medium Priority
?
498 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 400 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 70

Accepted Solution

by:
Scott Pletcher earned 1600 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

649 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