May 6, 2008 at 10:19 am
Hi friends,
We have triggers on each table in oracle database and none in sqlserver so far. But we are looking at creating triggers on sqlserver db's as well and I am trying to find the equivalent syntax for T sql..
In ORACLE, for edi_invoice column on EDI table:
CREATE OR REPLACE TRIGGER edi_trg
before insert or update ON edi for each row
begin
:new.EDI_INVOICE:= nvl(:new.EDI_INVOICE,' ');
end
/
I think in T SQL is should be like below?
CREATE TRIGGER edi_trg
ON edi
for insert or update
as
:new.EDI_INVOICE:= nvl(:new.EDI_INVOICE,' ');
go
Thanks much for all your help.
May 6, 2008 at 4:24 pm
Well, there's a lot of differences between triggers in Oracle and SQL Server, such as:
1) SqlServer triggers as always per-statement and never per-row. So if one UPDATE statement modifies 1000 rows, the trigger only fires once.
2) There are no "before" triggers, only "after" and "Instead Of" triggers.
3) There is no ":new" etc., objects; use "inserted", "deleted" pseudotables and the base table instead.
4) "nvl()" function is replaced by TSQL IsNull() function or the Ansi standard Coalesce() function (which I would recommend).
Your translated trigger would look something like this:
CREATE TRIGGER edi_trg
ON edi
After insert or update
as
Update edi
Set EDI_INVOICE = Coalesce(inserted.EDI_INVOICE, ' ')
From edi
Inner Join inserted ON inserted.PK = edi.PK
go
Though this might be slightly more efficient:
CREATE TRIGGER edi_trg
ON edi
After insert or update
as
Update edi
Set EDI_INVOICE = ' '
From edi
Inner Join inserted ON inserted.PK = edi.PK
Where edi.EDI_INVOICE is Null
go
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2008 at 6:23 pm
Thank you very much, that helped me a lot!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply