Trigger: Adding Data with data from another table

Last Post 03 Oct 2006 01:42 AM by CSalvatore. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
CSalvatore
New Member
New Member

--
02 Oct 2006 02:25 AM
Hi all,

Basics: After I add a record into table, I need to create several records into a table based on the inserted data and data from another table.

Since I'm a starter with Stored Procedures I couldn't get a good starting point for this with the Online Book until now. So I post my problem here.

Simply after inserting one record (into table1) I need to get all related records from a second table (table2). Then combine these and insert them into a third table (table3). My Problem is now how to implement this as a trigger, because I have to add several hunders records into the third table. I don't want to implemet this into the Client based application. I could do this relatively simply in VB, but how to solve this in T-SQL?

Have someone a starting point or how to implement a thing like a For Each construct in T-SQL.

I tried with INSERT INTO table3 SELECT column1 FROM table2 WHERE ..., but for every record I have to insert into table3 I need add a value from the inserted row in table1. So a construct like INSERT INTO table3 SELECT column1, inserted.col1 FROM table2 WHERE ... doesn't work. For implement this I need to join four tables, but you know: keep it a simple as possible, so I like to avoid this way.

I hope it's clear what I need. I think there could be a very simple solution, But I don't know how.

Please help.

Regards
Salvatore

mwesch
New Member
New Member

--
02 Oct 2006 03:29 AM
You didn't provide the full SQL statement you tried inside your trigger, but you are on the right path. Inside of a trigger, you have access to 2 "temporary" tables called INSERTED and DELETED. INSERTED contains the "new" versions of any records updated by the original insert/update statement, and DELETED contains the "old" versions of any records updated/deleted by the original insert/update/delete statement.

So, to do what you ask, you can try something like this.

---------------------------------------
create trigger tr_InsertTable1
on Table1
for insert
as

insert Table3
select {MyFieldList}
from INSERTED a
inner join Table2 b
on a.PkeyField = b.FKeyField
---------------------------------------

If you continue to have problems, provide the full SQL statement and someone will help from there.
CSalvatore
New Member
New Member

--
02 Oct 2006 04:45 AM
Michael

Because the INSERTED and the table2 has no direct relation to each other, I have to join two more tables for this solution. Because I tried to keep it as simple as possible, I wanted to avoid this.

But it seems to be the best way to do it for me anyway.

Thanks for your help.

If it doesn't work, I come back.

Have a nice time.
Salvatore
CSalvatore
New Member
New Member

--
03 Oct 2006 01:42 AM
Michael,

your proposal doesn't work. Perhaps it's only in my situation. I have to check which row has changed. I tried this with IF and CASE constructs, but SQL Query Analyzer tell's me about Syntax errors and that the inserted table doesn't exists. So now I've created three triggers:

-- Add UpdateStatus Records
-- Check for OS Update and add corresponding UpdateStatus records
CREATE TRIGGER tr_addOSUpdate
ON Updates AFTER INSERT AS IF UPDATE(OSID)
BEGIN
DECLARE @InsertedUIDOS INT
SELECT @InsertedUIDOS = (SELECT OSID FROM inserted)
IF @InsertedUIDOS>1
BEGIN
INSERT INTO UpdateStatus (ServerID, UpdateID)
SELECT Servers.ServerID, Updates.UpdateID FROM Servers INNER JOIN OperatingSystems Os ON Servers.OSID = Os.OSID INNER JOIN Updates ON Os.OSID = Updates.OSID WHERE Os.OSID=@InsertedUIDOS
END
END
GO
-- Check for Application Update and add corresponding UpdateStatus records
CREATE TRIGGER tr_addAppUpdate
ON Updates AFTER INSERT AS IF UPDATE(Update4AppID)
BEGIN
DECLARE @InsertedUIDApp INT
SELECT @InsertedUIDApp = (SELECT Update4AppID FROM inserted)
IF @InsertedUIDApp>1
BEGIN
INSERT INTO UpdateStatus (ServerID, UpdateID)
SELECT Servers.ServerID, Updates.UpdateID FROM Servers INNER JOIN InstalledApps IA ON Servers.ServerID = IA.ServerID INNER JOIN Updates ON IA.AppID = Updates.Update4AppID WHERE IA.AppID=@InsertedUIDApp
END
END
GO
-- Check for Service Update and add corresponding UpdateStatus records
CREATE TRIGGER tr_addSvcUpdate
ON Updates AFTER INSERT AS IF UPDATE(Update4ServiceID)
BEGIN
DECLARE @InsertedUIDSvc INT
SELECT @InsertedUIDSvc = (SELECT Update4ServiceID FROM inserted)
IF @InsertedUIDSvc>1
BEGIN
INSERT INTO UpdateStatus (ServerID, UpdateID)
SELECT Servers.ServerID, Updates.UpdateID FROM Servers INNER JOIN InstalledService ISvc ON Servers.ServerID = ISvc.ServerID INNER JOIN Updates ON Updates.Update4ServiceID = ISvc.ServiceID WHERE ISvc.ServiceID=@InsertedUIDSvc
END
END
GO

The IF @Var > 1 is there because a column with 1 is a placeholder.
This way it works without a problem. I don't know if there is a better way to do it.
Let my know if you have any suggestion to do it better.

Have a nice day.
Salvatore

mwesch
New Member
New Member

--
03 Oct 2006 02:48 AM
One thing with your trigger is that it will only work if you insert 1 record at a time. This may be fine for your UI, but if your DBA inserts multiple rows through some other method, then your trigger will fail.

I don't understand what you mean when you say you have to check which row has changed. These are all INSERT triggers, which means that the entire new record will be in the INSERTED table. The record inserted into the UPDATES table from your UI will have an identical matching record in the INSERTED table, so just replace UPDATES with INSERTED in your trigger.

It appears to me that this trigger will work and support multiple record insert statements - similar to my original example, but with the extra join as your most recent post specified.


---------------------------------------
CREATE TRIGGER tr_addOSUpdate
ON Updates AFTER INSERT AS
INSERT INTO UpdateStatus (ServerID, UpdateID)
SELECT Servers.ServerID, INSERTED.UpdateID
FROM Servers
INNER JOIN OperatingSystems Os
ON Servers.OSID = Os.OSID
INNER JOIN INSERTED
ON os.OSID = INSERTED.OSID
You are not authorized to post a reply.

Acceptable Use Policy