Difference between the syntax of a trigger written in Oracle

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.

All Forums

Old Forums

CLOSED - General SQL Server

Difference between the syntax of a trigger written in Oracle

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-10 : 07:52:02

Sandeep wagh writes "Sir,

I am doing a project concerning a bank application.It so happened that my colleague wrote a trigger in oracle * plus.He is well conversant with oracle but not with SQLSERVER syntax.Though i have gone through Sql and all its rules,i am just an amateur programmer....just started out.I have designed all the tables,established relationships between them,designed views...etc.My only problem lies in converting the syntax of the oracle trigger into SQLSERVER 2000 syntax.I've tried it several times but failed.Please can you solve my problem!!! i am sending you the trigger code which updates "savings table" or "currentac" or "fixed table" depending upon accounttype variable value.

create or replace trigger trig_acc
after insert or update on accounts
for each row
declare
acc accounts.aid%type;
attyp accounts.atid%type;
vcusid customer.cid%type;
bal customer.opbal%type;
dat customer.opdate%type;
intid interest.irid%type;

BEGIN
acc:=:New.aid;
attyp:=:New.atid;
intid:=1;
if inserting then
vcusid:=:new.cid;
else
vcusid:=:old.cid;
end if;
select opbal into bal from customer where cid=vcusid;
select opdate into dat from customer where cid=vcusid;
IF attyp=1 THEN
insert into savings(savingsid,aid,savingsdate,savingsbal,irid)
values (sav.nextval,acc,dat,bal,intid);
ELSIF attyp=2 THEN
insert into currentac(currentid,aid,currentdate,currentbal)
values (cur.nextval,acc,dat ,bal);
END IF;
END;"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-10 : 08:35:03

You should post the DML of the trigger that you have tried to write and explain how if fails to meet your needs. It may also be helpful to post the DDL of the Accounts and the other tables.

No one is going to do you work for you for free, but people will gladly help you debug your own efforts ...

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-10 : 10:05:40

Oracle tends to be coded in cursors whaereas sql server uses set based operations. Don't know if this is intrinsic to Oracle or because the developers aren't very good.

>> for each row
You should rarely see this in sql server

This just seems to be updating a couple of tables from the inserted data and so should probably be a couple of statements.

See this for a start
www.nigelrivett.com
Triggers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 11:19:04

My understanding is that Oracle uses Refernece Cursors...These are akin to a standard select in sql server, such that when a fromt end app calls the package.procedure it is a refernece to a pointer (which happens to be a cursor or select statememt) and the results are grabbed by the front end as needed. All other operations (updates/ Deletes) are set based. The selects are actually set based (sort of) and efficiently refernce the data, without having to actually move it anywhere, except when it's needed. For example:

--@Q:\Benefits\Packages\Ben_GetCodeTables_Package.pkg;
CREATE OR REPLACE PACKAGE Ben_GetCodeTables_Package
AS

TYPE CurRefType IS REF CURSOR;

PROCEDURE Get_Country_sp (CountryCur IN OUT CurRefType);
END Ben_GetCodeTables_Package;

/

CREATE OR REPLACE PACKAGE BODY Ben_GetCodeTables_Package
AS
-- *********************************************************************
-- *** P R O C E D U R E (Get_Country_sp) D E C L A R A T I O N S *****
-- *********************************************************************
PROCEDURE Get_Country_sp (CountryCur IN OUT CurRefType) IS
BEGIN

OPEN CountryCur FOR SELECT COUNTRY
,DESCR
,DESCRSHORT
FROM PS_COUNTRY_TBL
WHERE COUNTRY = 'USA';

EXCEPTION
WHEN OTHERS THEN
RAISE;

END Get_Country_sp;

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-10 : 23:49:58

CREATE TRIGGER trig_acc ON accounts FOR INSERT, UPDATE AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM deleted)
 BEGIN 
  INSERT INTO savings(aid,savingsdate,savingsbal,irid) 
  SELECT D.AID, C.opdat, C.opbal, 1 FROM deleted D INNER JOIN Customer C ON D.cid=C.cid
  WHERE EXISTS (SELECT * FROM inserted WHERE cid=D.cid AND atid=1)
 
  INSERT INTO currentac(aid,currentdate,currentbal) 
  SELECT D.AID, C.opdat, C.opbal FROM deleted D INNER JOIN Customer C ON D.cid=C.cid
  WHERE EXISTS (SELECT * FROM inserted WHERE cid=D.cid AND atid=2)
 END
ELSE
 BEGIN
  INSERT INTO savings(aid,savingsdate,savingsbal,irid) 
  SELECT I.AID, C.opdat, C.opbal, 1 FROM inserted I INNER JOIN Customer C ON I.cid=C.cid
  WHERE EXISTS (SELECT * FROM inserted WHERE cid=I.cid AND atid=1)

  INSERT INTO currentac(aid,currentdate,currentbal) 
  SELECT I.AID, C.opdat, C.opbal FROM inserted I INNER JOIN Customer C ON I.cid=C.cid
  WHERE EXISTS (SELECT * FROM inserted WHERE cid=I.cid AND atid=2)
 END

I'd recommend testing it thoroughly to see if it works, I'm not guaranteeing it will but it should get you pretty close.

There's an assumption that the currentid and savingsid columns are declared as identity in SQL Server, if this is not the case then this trigger may not work.

Edited by - robvolk on 04/10/2003 23:52:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources