vanishing triggers

[kevb]

I am experincing problems with triggers disappering from my database. I think it happens when tables are changed. Does anyone else have this problem or know how to prevent it?

 

[Marco Kalter]

For views this is a known Oracle bug. An instead-of trigger will be dropped when you recompile a view.

For tables this will only happen if you actually drop the table. If you select "recreate table" in the table definition editor, then the trigger(s) will indeed be dropped (as described).

------------------
Marco Kalter
Allround Automations

 

[kevb]

Thanks for reply, it is views I am working with.

 

[kevb]

Are you sure this is not just an issue in plsql/developer.

I have asked a oracle expert who as never heard of this problem, and posted messages on oracles own bb, and recieved the following replys

"Hi,

PLSQL developer does not recognise triggers on views.

If you edit the view with PLSQL developer, the triggers will not be created as a part of the create or replace script.

You could create a script that recreates any view triggers, so that it can be run after any PLSQL developer recompiles."

"Hi,

I think that the problem is in the product.
That there is no concideration that there could be a trigger on a view, so they are not taken into account.

I use Designer 9, and because it creates the view and then the triggers separately there is never a problem.

But Designer is not a useful tool for tweaking a view in the same way that PLSQL developer is.

So: 'tweak' in PLSQL developer, cut and paste into Designer and generate out. A bit long winded."

 

[Marco Kalter]

The problem is that the "create or replace view" command drops the trigger. This is not really a PL/SQL Developer issue, though it may be necessary to provide a work around.

------------------
Marco Kalter
Allround Automations

 

[kevb]

Some kind of work around would be great.

Would it be possible to flag up a warning when editing a view that has triggers related to it, or perhaps when editing the view to also open any triggers related to it for recompiling?

Regards

 

[admin]

Originally posted by mkalter:
The problem is that the "create or replace view" command drops the trigger. This is not really a PL/SQL Developer issue, though it may be necessary to provide a work around.

'Create or replace view' command preserves grants only, not triggers. It is a brand new View for Oracle.
Marco, you are right - dissaperance of 'INSTEAD OF' triggers is Oracle (not really PL/SQL Developer) issue. It's known for a long time.
Workaround could be attach the 'Instead of' trigger to view script when Export User Objects - or keep it in a separate external scripts. However, in this scenario it is user onus to keep trigger always updated.
Install of CVS could be another alternative solution.

 

[kevb]

Marco

You mentioned the possibilty of a work around for this oracle issue.

Is this still a possibilty, will this be added in version 6.

Regards

Kev

 

[Marco Kalter]

This is indeed on the to-do list.

 

[admin]

Nothing has been done yet on this one (or I've missed something)...
Could be possible to attach instead of triggers to view script when a script exported? I want to keep view and instead of triggers together in one script.

 

[Marco Kalter]

This has been addressed. When you compile a view source in a SQL Window, the triggers are preserved. If this does not work for you, let me know.

 

[admin]

Compiling in SQL Window works just fine. Thank you. However, it does not solve my problem. I often run the updates using scripts in SQL +; if I forget that trigger is 'hooked' to view I will loose it anyway.

Is it possible to give an option to combine view and instead of trigger in one script when a view is exported?

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[admin]

Could you let me know when it will be implemented ie. combine view and instead of trigger in one script when a view is exported?

Is it possible to display an instead of trigger(s) along with a view definition ?

 

[Cal Gordy]

I really think you should implement a full 'editor' for views where you can take full control of the process. Views have enough moving parts with instead of triggers, aliases, comments that a full plsql editor for views would provide a very useful interface. You have one for synonyms of all things - while views would be much more useful.

 

[Marco Kalter]

I agree that it would indeed be convenient to edit a view as a whole.