DISABLE TRIGGERS 1

[fmsousa]

Hi,
I would like to know, if possible, how can i disable a trigger inside a stored procedure.

I know that the following instruction disable the trigger (but it won't work inside a strored procedure):

ALTER TRIGGER <name_trigger> DISABLE;

Thanks in advance.
Best regards,
Fernando Sousa

 

[rcurva]

You have to use dynamic SQL or dbms_sql utility.

For example (Oracle 7):

--add in your declaration
V_CURSORID INTEGER;
V_NUMROWS INTEGER;
V_TRIGSTRING VARCHAR2(1999);


-- add inside the program part
V_CURSORID := DBMS_SQL.OPEN_CURSOR;
V_TRIGSTRING := 'ALTER TRIGGER mytrig DISABLE';
DBMS_SQL.PARSE(V_CURSORID, V_TRIGSTRING, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(V_CURSORID);
COMMIT;

For Oracle 8, you can use EXECUTE IMMEDIATE.

 

[fmsousa]

Thanks rcurva! It works!