Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
axenos
Starting Member
2 Posts |
Posted - 2013-02-28 : 11:20:27
|
Hello all,I am new to trigger in sqlserver and it would be very helpful to help convert this oracle sql trigger to a MS SQL one.create or replace trigger "FIM_AGILR_AFTER_UPDATE" after update on FIM_AGILE for each row declare found boolean := false;begin for x in (SELECT internal_id FROM FIM_AGILE_CHANGES WHERE internal_id = :new.internal_id) loop found := true; exit; end loop; if not found then insert into FIM_AGILE_CHANGES values (:new.internal_id, 'Modify'); end if;end;Thank you in advance,Andreas |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 12:57:29
|
looks like you're using oraclethis is ms sql server forumplease try your luck at oracle forums like www.orafaq.com------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 12:57:30
|
looks like you're using oraclethis is ms sql server forumplease try your luck at oracle forums like www.orafaq.com------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 13:46:52
|
Translation to T-SQL would be something like shown below. However, either I am not understanding the logic in the Oracle code (which is very likely), or the logic is not quite right. In any case, here is my attempt:CREATE TRIGGER dbo.FIM_AGILR_AFTER_UPDATEAFTER UPDATE ON FIM_AGILEAS IF NOT EXISTS ( SELECT * FROM FIM_AGILE_CHANGES f INNER JOIN INSERTED i ON i.internal_id = f.internal_id ) INSERT INTO FIM_AGILE_CHANGES SELECT INSERTED.INTERNAL_ID, 'Modify' FROM INSERTED;GO The concern I have relates to the fact that a single update can affect many rows, and some of the updated rows may be in FIM_AGILE_CHANGES and some may not be. In that case, the logic as I understood, and as I have translated it will not insert any rows into the FIM_AGILE_CHANGES table. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 23:16:36
|
quote: Originally posted by James K Translation to T-SQL would be something like shown below. However, either I am not understanding the logic in the Oracle code (which is very likely), or the logic is not quite right. In any case, here is my attempt:CREATE TRIGGER dbo.FIM_AGILR_AFTER_UPDATEAFTER UPDATE ON FIM_AGILEAS IF NOT EXISTS ( SELECT * FROM FIM_AGILE_CHANGES f INNER JOIN INSERTED i ON i.internal_id = f.internal_id ) INSERT INTO FIM_AGILE_CHANGES SELECT INSERTED.INTERNAL_ID, 'Modify' FROM INSERTED;GO The concern I have relates to the fact that a single update can affect many rows, and some of the updated rows may be in FIM_AGILE_CHANGES and some may not be. In that case, the logic as I understood, and as I have translated it will not insert any rows into the FIM_AGILE_CHANGES table.
it should beCREATE TRIGGER dbo.FIM_AGILR_AFTER_UPDATEAFTER UPDATE ON FIM_AGILEAFTER UPDATEAS INSERT INTO FIM_AGILE_CHANGES SELECT INSERTED.INTERNAL_ID, 'Modify' FROM INSERTED i WHERE NOT EXISTS (SELECT 1 FROM FIM_AGILE_CHANGES WHERE internal_id = i.internal_id)GO This will ensure only rows not already there in CHANGES table will get populated------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
axenos
Starting Member
2 Posts |
Posted - 2013-03-01 : 04:51:56
|
Thanks a lot guys (and also for the quick reply).It really helped.Andreas |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 05:05:11
|
you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|