SQL Trigger
-
Hi,
Can someone point me in the right direction with regards to using triggers.
I am using MS SQL 7.0, created a database with three tables, A, B and C. Table A is populated with data via a scheduled job that reads data from an access table. When Table A is INSERTED with data, I would like a trigger to do a comparason between it and Table B. Any records in Table A that does not exist Table B, should be added to Table C.
Table C is therefore a difference table.
However more then one record may be INSERTED into Table A via the scheduled job and this is my problem, since the trigger I created does not create a difference list.
Can anyone help?
Thanks in advance
-
Triggers are fired once for each statement, not for each row affected. The inserted table will hold all the rows inserted by a statement so all you need to do is:
INSERT INTO TableC
SELECT * FROM inserted
WHERE PrimaryKey NOT IN (SELECT PrimaryKey FROM TableB)
That's at least the general logic if I understand your post.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thank you for a speedy reply to my post.
Just to clarify one thing; I have created a DTS package that is scheduled to run periodically. The package performs the task of inserting data into Table A.
Question, at what point in the insertion process would the trigger be fired?
From your responce Table A is not used, rather the created 'inserted' table.
Okay I'll try this out.
-
R u using a bulk insert statement or just standard sql query?
-
The DTS package uses a Data Pump task that opens an access MDB table, extracts the data into an MS SQL table and then terminates.
This seems to be simillar to a Bulk insert as my trigger doe not fire when the process terminates.
-
Here's something that can help you :
bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "CHECK_CONSTRAINTS"
BULK INSERT pubs..authors2 FROM 'c:\authors.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
CHECK_CONSTRAINTS
)
the CHECK_CONSTRAINTS keyword tells sqlserver to use the check constraints (including triggers)
-
But, at the first time you must create the Trigger by using the "inserted" table. The "inserted" table is a built-in table every update-or-insert trigger uses. In the INSERT case, it temporary saves all the data ready to be inserted (and not commited). But keep in mind SQLServer not supports BEFORE triggers, so in your transaction's context insert data is already in destination table.
Jorge
-
I decided that it would be easier to abandon implementing a trigger and to run everything from within a DTS package.
Thanks to everyone who responded to my post, your help was appreciated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply