Create Insert Trigger

byron · November 19th, 2003, 05:06 AM

Hi,

I'm new to creating triggers so I could use some help if possible.

I need to create a trigger that when a row is added to 1 table, it reads the contents list table, then loops through the list adding to a transaction table, the id of the newly created record and the id of the list item.

Can anybody please help.

Thanks in advance

Jeff Mason · November 19th, 2003, 08:12 AM

Could you be a bit more specific about the tables you are using and the operations you want to perform? I'm having a hard time parsing your "requirements".

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

planoie · November 19th, 2003, 08:20 AM

To create the trigger you would use syntax like this:

CREATE TRIGGER <trigger name>
ON <table name>
FOR INSERT
AS
BEGIN
<trigger body>
END

Within the body of your trigger you can called a table called INSERTED. This is a structurally identical table as the table in "ON <table name>" but contains only the row you inserted.

You could then perform the actions necessary using that table as you would any other.

Not knowing what you are referring to by "the list item" I can't really offer more than a little sample of syntax within the trigger. Form example, let's say we wanted to add a new row to another table based on a value from the table that has the trigger:

INSERT INTO anotherTable
SELECT triggerTable.aColumn, <other columns>
FROM INSERTED JOIN anotherTable ON INSERTED.rowIdColumn=anotherTable.rowIdColumn

I hope this is clear. If you want more specific help, post the table structure and some more detail.

Peter
------------------------------------------------------
Work smarter, not harder.

byron · November 19th, 2003, 09:51 AM

Thanks for your replies but I'm still not sure on the loop. I'll go into more depth of the scenario below

Table1 (call T1) gets a row inserted which fires the trigger
idField = t1ID

I then need to obtain all records from table 2 (Unrelated) to list
their IDS. (There are only about 5 records therefore returning T2IDs 1 to 5)

For each record in T2 I need to populate a third table as below
Insert into thirdtable (ID1,ID2) values (t1id,T2id)

Any help would be great so I can grasp the principle.

Cheers

b

robsevcik · November 19th, 2003, 11:00 AM

may be this helps...

Code:

create trigger trYourTrigger on T1
for insert
as

insert into T3 (T1ID, T2ID) select i.ID, T2.ID
from inserted i left join T2 on <some condition>

-- [email protected]

planoie · November 19th, 2003, 11:11 AM

You should try to avoid writing looping code in SQL. Try to think in terms of row sets in SQL. There is little you need to do with loops. An insert that uses a select (in places of VALUES) can insert multiple records.

Robert's suggestion looks correct, but you mentioned that your second table is unrelated, so you should only need this:

INSERT INTO T3 (T1ID, T2ID)
SELECT i.ID, T2.ID
FROM INSERTED i, T2

This would select every row from T2 for each row in INSERTED. Because you only have 1 row in INSERTED, you'll only get the 5 records from T2 once.

Peter
------------------------------------------------------
Work smarter, not harder.

byron · November 19th, 2003, 11:30 AM

Cracked it. Thanks everyone for your help.

Cheers

b