Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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

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
JameMeck
Asked:
JameMeck
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
JameMeckAuthor Commented:
Thanks!

I am using it, it is running well.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now