March 10, 2014 at 11:38 pm
Hi I am new to MSSQL server. I have problem in migration from ORACLE trigger to MSSQL server. Here below the trigger in Oracle..
<<--------Trigger Begin----------->>
create or replace
TRIGGER transactiontrigger After INSERT ON ORDERINVOICE
FOR EACH ROW
BEGIN
update A_ACCOUNT
set A_ACCOUNT.AC_RUNNING_BLC=(A_ACCOUNT.AC_RUNNING_BLC-:new.NETAMMOUNT+:new.COMMISION-:new.TDS)
where A_ACCOUNT.A_ID=:new.A_ID;
END;
<<---------Trigger END-------->>
Could you some one help me to transform the trigger in MSSQL compatible.
Nilotpalhazarika
March 11, 2014 at 6:20 am
There's no FOR EACH ROW syntax in SQL Server triggers. What you have instead are two tables called inserted and deleted that contain ALL rows affected by the transaction. Something along these lines should point you in the right direction, obviously you'll need to do your own verification of this:
CREATE
TRIGGER transactiontrigger ON ORDERINVOICE
AFTER INSERT
AS
BEGIN
UPDATE A_ACCOUNT
SET A_ACCOUNT.AC_RUNNING_BLC = ( A_ACCOUNT.AC_RUNNING_BLC
- inserted.NETAMMOUNT
+ inserted.COMMISION
- inserted.TDS )
FROM A_ACCOUNT
INNER JOIN INSERTED ON A_ACCOUNT.A_ID = inserted.A_ID;
END;
March 11, 2014 at 6:50 am
CREATE TRIGGER transactiontrigger ON ORDERINVOICE
AFTER INSERT
AS
BEGIN
UPDATE A_ACCOUNT
SET A_ACCOUNT.AC_RUNNING_BLC=
(A_ACCOUNT.AC_RUNNING_BLC - INSERTED.NETAMMOUNT + INSERTED.COMMISION - INSERTED.TDS)
FROM A_ACCOUNT INNER JOIN INSERTED ON A_ACCOUNT.A_ID = INSERTED.A_ID
END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply