Trigger with External Logging

[NHW]

Hi all. I'm new to writing triggers and simply couldn't think of getting something to work.

I am using DTS to import a csv file into a table called "tblChild". There is only one column, "ParentID", to insert. Some ParentID may already exist in the current tblChild. So the trigger should only add those ParentID that are new. In addition, a row should be added only if the ParentID also exists in a table in another database (dbParent.tblParent.ParentID). If it doesn't exist, write to a text file for logging.

Can triggers handle these tasks? Thanks in advance.

-NHW

 

[JohnDTampaBay]

You can do it all with T-SQL. Use bcp to import the CSV file into a staging table. Then INSERT the new parents that are in the remote table.

INSERT tblChild(ParentID)
SELECT s.ParentID
FROM tblStaging s
  INNER JOIN dbParent.tblParent r
    ON r.ParentID = s.ParentID
WHERE NOT EXISTS(SELECT * FROM tblChild c
                 WHERE c.ParentID = s.ParentID)

Then use bcp to log the non-existent ParentIDs using this query.

SELECT s.ParentID
FROM tblStaging s
WHERE NOT EXISTS(SELECT * FROM dbParent.tblParent r
                 WHERE r.ParentID = s.ParentID)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[NHW]

Thanks John. But is it possible to log the non-existing ParentID into a text file as this information may need to be viewed by users who don't have access to the database.

 

[JohnDTampaBay]

Use bcp to log the non-existent ParentIDs using this query.

Check out BCP in Books Online. It will allow you to quickly export the result set of a query to a text file.

SELECT s.ParentID
FROM tblStaging s
WHERE NOT EXISTS(SELECT * FROM dbParent.tblParent r
                 WHERE r.ParentID = s.ParentID)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)