CODEWITHSUNDEEP
oracle-databasetriggers
Lajos Arpad
Lajos Arpad

Reputation: 76583

How to convert a varchar to a table name?

I have an interesting question: I have a table with trigger names, called Foo and the Foo.TriggerName field stores trigger names. If I delete a row from Foo, I would like a trigger which would drop the trigger with the name Foo.TriggerName.

Example:

Foo
ID, TriggerName
1,  'Lorem'
2,  'Ipsum'
3,  'Bar'

When I am deleting the row with ID = 3, I would like to automatically delete the trigger with the name 'Bar'. How can I achieve this?

Upvotes: 0

Views: 142

Answers (1)

kochobay
kochobay

Reputation: 392

You can try to use dynamic sql

You can define a trigger for delete on Foo and in trigger you can do dynamic sql call

CREATE OR REPLACE TRIGGER foo_before_delete
BEFORE DELETE
    ON Foo
    FOR EACH ROW
BEGIN

EXECUTE IMMEDIATE 'DROP TRIGGER '||:old.TriggerName||';';

END;

Upvotes: 3

Related Questions