Solved

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

Posted on 2013-01-20
3
492 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
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:
ScottPletcher 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now