Solved

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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