Insert Trigger

[DCSage]

I created a package in SSIS which inserts data into a table from a flat file. The table is Logs_ASRUN. I am planning for the fact that the flat file the user pulls may have records that already exist in the table. So I created another table called Logs_ASRUN_all, whcih should have the clean data. Since my operation is being performed in a package, is it possible to create a trigger that will insert data into this clean table based on existence?

I have the following trigger, but it is incorrect as no data was inserted:

if exists (select PrdNumber from Logs_Asrun Group By PrdNumber  Having count(*) = 1)

		INSERT INTO Logs_Asrun_All (Title, PrdNumber, Network, ScheduleDate)
		SELECT Title, PrdNumber, Network, ScheduleDate FROM INSERTED where prdNumber is not null

can someone direct me to a good resource on ssis? for my package, I want to integrate 2 processes - pull from 2 flat files and insert into 2 different tables. I just created 2 separate packages. is this even possible?

 

[starsky51]

Hi.
Your if exists query isn't checking the PrdNumber against anything, so it will always come back true.

You should use an INSTEAD OF trigger doing something like:

IF EXISTS (SELECT Logs_Asrun.PrdNumber FROM Logs_Asrun INNER JOIN INSERTED ON Logs_Asrun.PrdNumber = INSERTED.PrdNumber)
BEGIN
INSERT INTO Logs_Asrun_All (Title, PrdNumber, Network, ScheduleDate)
SELECT Title, PrdNumber, Network, ScheduleDate FROM INSERTED where PrdNumber is not null
END
ELSE
BEGIN
INSERT INTO Logs_Asrun (Title, PrdNumber, Network, ScheduleDate)
SELECT Title, PrdNumber, Network, ScheduleDate FROM INSERTED where PrdNumber is not null
END

 

[DCSage]

the trigger is still not inserting anything into the table logs_asrun_all. when the ssis package executes, only the main table, logs_asrun, is receiving the inserted records. is there another way to approach the trigger perhaps within the package?

 

[DCSage]

This works perfectly. I did the following:

1) I have an OLE DB Destination Data Flow Component. In the properties of this component, I typed, Fire_Triggers in the FastLoadOptions.

good you direct me to a good resource on ssis? for my package, I want to integrate 2 processes - pull from 2 flat files and insert into 2 different tables. is this possible? Since i am not sure if I can integrate this into 1 package, I just created 2 separate packages.

 

[SQLSister]

Yes it is possible to do that in one package. SSIS is complicated and not easy to learn. I suggest you get a book on it, I have found this one helpful:

http://www.amazon.com/Professional-...=sr_1_3?ie=UTF8&s=books&qid=1277472967&sr=8-3

"NOTHING is more important in a database than integrity." ESquared