Change trigger from SQL SERVSER to ORAC

[trungx]

I change trigger and procedure from SQL SERVER to ORAC but I can do it.

Ex:
CREATE PROCEDURE quest2001.proc_setstatus
is
mid number(30);
param varchar2(4);
taction varchar2(10) := '';
status varchar2(30) :='Nused';
flag number(10);
Begin
IF param= 'BNOT' then
SELECT q.IDTBNOT into flag FROM quest2001.QUEST_TBNOTE q WHERE q.IDTBNOT=mid;

IF flag > 0 then
status:='Used';
UPDATE quest2001.TYPE_BLOC_NOTE t SET t.STATUTTBNOT =status WHERE t.IDTBNOT=mid;
END IF;
End if;
end;
/* end of procedure
this Procedure I create in ORAC
*/

CREATE TRIGGER quest2001.STATUTEVAFONCTi ON quest2001.EVALUATIONDECLARE
FOR INSERT,UPDATE
AS
DECLARE @id varchar(30)
(SELECT id=old.IDFONCT FROM deleted old)
EXECUTE quest2001.PROC_SETSTATUS id,'EIDX;'

this trigger created in SQL SERVER I can not change ORAC.

Trungx

 

[rcurva]

I don't know what you are trying to accomplish on this part of your script..

(SELECT id=old.IDFONCT FROM deleted old)
EXECUTE quest2001.PROC_SETSTATUS id,'EIDX;'

But, if you are trying to pass a parameter here, you should update your procedure to accept parameters.

For example;
CREATE PROCEDURE quest2001.proc_setstatus (mid number, param varchar2)
is ...

The trigger will become;

CREATE OR REPLACE TRIGGER quest2001.STATUTEVAFONCTi
AFTER INSERT OR UPDATE
ON quest2001.EVALUATIONDECLARE
FOR EACH ROW
DECLARE
v_id varchar(30);
BEGIN
SELECT o.IDFONCT into v_id
FROM deleted o;

EXECUTE quest2001.PROC_SETSTATUS(v_id,'EIDX');
END;
/

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War