trigger - passing affect row (index) to a variable

[tickko]

i'm looking for some assistance on a trigger i'm developing. upon update, i need a trigger to update tables in oracle based on an index and information in a table in sql. the problem is, how do you pass the index from the updated row in sql into the trigger?

--------------
create trigger trgrmupdate on dbo.room
for update as
declare @rmkey nvarchar(10),
@rmid nvarchar(20)

select @rmkey = rowkey from room

set @rmid = (select roomid from room
where rowkey = @rmkey)


this works but it passes the the last row of the column, not the row affected.

thanks,
jb

 

[JohnDTampaBay]

Two virtual tables are accessible within a trigger: Inserted and Deleted (updates are a combination of the two statements). These virtual tables only exist during the transaction and have the same structure as the table being modified. The Inserted table contains the new data; the Deleted table contains the old data.

Join to either of tables to "touch" the rows being modified. If you need a hand implementing the JOIN, please post the rest of the trigger code.

Good luck! --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tickko]

thanks angel!

thats exactly what i was looking for. would you suggest using the join command or the update command to insert the updated info into the other tables?

jb

 

[JohnDTampaBay]

That all depends on what data you need to insert into the "other tables" on Oracle. The trigger described applies to a single SQL table and your posts refer to Oracle tables. This implies that there is more information needed for insertion on Oracle than this SQL table provides, ergo a JOIN.

Here's an example of an UPDATE to a denormalized table containing Name and Address and the TRIGGER that fires and UPDATEs data in normalized tables for Names and Addresses:

UPDATE People 
SET FullName = 'Angel',
  Address '123 Main St'
WHERE PersonID = 1

This UPDATE fires the following trigger:

CREATE TRIGGER trgUpdateNameAddress
FOR UPDATE AS
BEGIN
  UPDATE Names
  SET PersonName = i.FullName
  FROM Inserted i
    JOIN Names n ON i.PersonID = n.PersonID

  UPDATE Addresses
  SET PersonAddress = i.Address
  FROM Inserted i
    JOIN Addresses a ON i.PersonID = a.PersonID
END

This may be over the top, but it hopefully shows how a trigger fired from a single table can affect related data in other tables. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tickko]

thanks angel!

will joins work with an inserted table if you have to concantenate variables together from the inserted table? even if not i understand the process of joins a little more.


thanks,
jb

 

[JohnDTampaBay]

Yep. I have used a join where I concatenated two columns from one table to produce the key for the second table. From within the trigger, the Inserted and Deleted tables act just like any other table, so I don't see why that wouldn't work.

Peace! --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.