I need to write an SQL function that disables all triggers

[tompinch]

I need to write an SQL function that disables all triggers
on a given table. The function should take a tablename
and its owner as arguments, and disables all triggers on it.
If all the triggers are disabled, the function returns 1.
Otherwise it should return -1.
?????????????????????????????

[Steve Jackson]

Usually a DBA disables the triggers with DML commands on the database
schema. I never heard of this being done with a function that can be
called from within the application. Seems like it would be a big
threat to the integrity of the data if application code should simply
disable triggers with a function call.

Steve Jackson

On 5 Feb 2001 19:38:51 -0800, "tompinch" <drillcore@hotmail.com>
wrote:

>
>I need to write an SQL function that disables all triggers
> on a given table. The function should take a tablename
>and its owner as arguments, and disables all triggers on it.
>If all the triggers are disabled, the function returns 1.
>Otherwise it should return -1.
>?????????????????????????????
>


Steve Jackson, Enterprise Section Leader
stephenjackson@compuserve.com

[kotta Satyamurthy]

Hi there,

You need to use Dynamic sql (it's very easy if you are working on Oracle
8i), since dynamic sql before Oracle 8i was bit complex.

If you are working on 8i then do the following :

1. Pass in the table name as Input paramter for the function
2. Inside the function declare a cursor that selects the triggers for the
table you are passing in as parameter from the data dict table : ALL_TRIGGERS

CURSOR c1 IS
SELECT trigger_name
FROM all_triggers
WHERE table_name = tab_name;

3. Open the LOop (for i IN C1)
and then using EXECUTE IMMEDIATE command (oracle 8i) you can include the
command in single quotes, for disabling the triggers. See the example below
which disables the constraints.

EXECUTE IMMEDIATE
'ALTER TABLE '||tab_name||' DISABLE CONSTRAINT '||i.constraint_name;

4. close the loop

Hope this will work for you, it will since it worked for me.




"tompinch" <drillcore@hotmail.com> wrote:
>
>I need to write an SQL function that disables all triggers
> on a given table. The function should take a tablename
>and its owner as arguments, and disables all triggers on it.
>If all the triggers are disabled, the function returns 1.
>Otherwise it should return -1.
>?????????????????????????????
>