Triggers silently dropped when view replaced



  • "Instead of" triggers on a view are silently dropped whenever a view is updated.  There is no way to prevent this from happening.



  • @tharpa said:

    "Instead of" triggers on a view are silently dropped whenever a view is updated.  There is no way to prevent this from happening.

    Be careful when you drop any db object. Also meke request to DBA team for restortion of any acidently droped objects.



  •  drop table dragon; // implemented necessary gui functionality



  • @ekolis said:

     drop table dragon; // implemented necessary gui functionality

     

    Dropping a table is a GUI feature?

    Wonderful.

     



  • Only when you need to implement drag'n drop :)



  • Glad I don't use Oracle anymore.



  • @tharpa said:

    "Instead of" triggers on a view are silently dropped whenever a view is updated.  There is no way to prevent this from happening.

    After NAgesh read oracle doc, I realize this behavioour is also same for tables. Drop table will drop triggers on table with NO WARNINGS.

    So why is this expected different?



  •  Hi, Nagesh, I saw your earlier comment, which was a non-sequitur, but I figured you were trying to stay in character.  This one was at least a legitimate question.  

    I'm not currently working in Oracle (SQL Server instead), so this is going from memory. 

     At least in Apex, you can easily see the triggers for a table in object browser.  For a view however, the (instead of) triggers can not be seen in object browser.

    Secondly, you mentioned dropping a table.  My post was not about a dropping a view, but about updating one.  In Apex you can perform minor changes on a table without side effects such as triggers, etc. being dropped.



  • @tharpa said:

     Hi, Nagesh, I saw your earlier comment, which was a non-sequitur, but I figured you were trying to stay in character.  This one was at least a legitimate question.  

    I'm not currently working in Oracle (SQL Server instead), so this is going from memory. 

     At least in Apex, you can easily see the triggers for a table in object browser.  For a view however, the (instead of) triggers can not be seen in object browser.

    Secondly, you mentioned dropping a table.  My post was not about a dropping a view, but about updating one.  In Apex you can perform minor changes on a table without side effects such as triggers, etc. being dropped.

    I am not oracle expert, but I got one guru in my team who claims he is.

    He just did a test on Oracle version: 9.2.0.7.0 and he is saying it is simply not true. The instead of trigger stay in place after the view has been modified.

    The tool he using is Alround automation PL/SQL Developer. He is going to try this in sqlplus next.

    In my view, you should not have any trigger on database at all, except only for audit purposes. All else should be managed by ORM tools and your application.



  • @tharpa said:

    My post was not about a dropping a view, but about updating one
     

    Do you have some proof-of-concept code that demonstrates the behaviour you described?

    @Nagesh said:

    Be careful before you drop any db object.

    FTFY. The care is not in dropping the object, it is in checking you are dropping the correct object (and associated

    @Nagesh said:

    Also meke request to DBA team for restortion of any acidently droped objects

    ... who will laugh at you and refuse your request, pointing you to your recycle bin.

    (or they could be nice because they've configured Flashback already, making restoration a simple operation.)

    @Nagesh said:

    In my view, you should not have any trigger on database at all, except only for audit purposes. All else should be managed by ORM tools and your application.

    An interesting view. Why do you advocate this? I can't think of any situation where event-driven data changes should be delegated to the application/ORM layer, away from the database.

     

     



  • I will present opinion on trigger. Some copy-paste from other experts, but I agree with views.

    Triggers are always cause of some maintaining code trouble. It will give headache to developers. Not only because of non-available of poor documentation, but also due to way trigger is written. Mutation is well known problem with oracle trigger. To just do something in one or two line of code require developer to create a package to maintain state and then go around mutation problem. This is main reason, I see trigger must be avoided like girl with chicken-pox in school.

    Another reason is documented by oracle expert Thomas Kite. He give example of sendmail functionality. User put an smtp mail service in trigger. Now this is get fired after the row is inserted. But if user rollback transaction, mail is already sent.

    Final reason, trigger is built-in functionality. Why use built-in stuff, if you can write your own implementations? This is sarcastic talk only



  • @Nagesh said:

    Triggers are always cause of some maintaining code trouble. It will give headache to developers. Not only because of non-available of poor documentation, but also due to way trigger is written.

    Do you mean no proper documentation about triggers, or no documentation and code policy governing your developers when they write/maintain triggers?

    @Nagesh said:

    Mutation is well known problem with oracle trigger. To just do something in one or two line of code require developer to create a package to maintain state and then go around mutation problem.

    I see this as dependency issues, rather than trigger problems.

    @Nagesh said:

    Another reason is documented by oracle expert Thomas Kite. He give example of sendmail functionality. User put an smtp mail service in trigger. Now this is get fired after the row is inserted. But if user rollback transaction, mail is already sent.

    Erm... he's set up a trigger against an INSERT statement, and the trigger's fired accordingly - how is that wrong? Or do you mean the trigger should have fired when the INSERT is committed?

     



  • @Cassidy said:

    @Nagesh said:

    Another reason is documented by oracle expert Thomas Kite. He give example of sendmail functionality. User put an smtp mail service in trigger. Now this is get fired after the row is inserted. But if user rollback transaction, mail is already sent.

    Erm... he's set up a trigger against an INSERT statement, and the trigger's fired accordingly - how is that wrong? Or do you mean the trigger should have fired when the INSERT is committed?

    And how, in any case, could the sending or non-sending of the message be more of a WTF than the fact that [i]somebody put an smtp mail service into a database trigger?[/i] Oh, I suppose there are a few situations where it might make sense (infrequent updates, but the mail must be pushed out [i]immediately[/i] when an update is made), but I'd find it hard to believe that you couldn't have, for example, a small table of pending mails which you write into with the trigger, and poll every X seconds in another process to get the next batch of mails to send.



  • @Scarlet Manuka said:

    @Cassidy said:

    @Nagesh said:

    Another reason is documented by oracle expert Thomas Kite. He give example of sendmail functionality. User put an smtp mail service in trigger. Now this is get fired after the row is inserted. But if user rollback transaction, mail is already sent.

    Erm... he's set up a trigger against an INSERT statement, and the trigger's fired accordingly - how is that wrong? Or do you mean the trigger should have fired when the INSERT is committed?

    And how, in any case, could the sending or non-sending of the message be more of a WTF than the fact that somebody put an smtp mail service into a database trigger? Oh, I suppose there are a few situations where it might make sense (infrequent updates, but the mail must be pushed out immediately when an update is made), but I'd find it hard to believe that you couldn't have, for example, a small table of pending mails which you write into with the trigger, and poll every X seconds in another process to get the next batch of mails to send.

    Ask Thomas Kite for mail example!. Do search for Tom - Trouble with trigers and you'll get link to his world-famous article.



  • I did that, and it was presented as a hypothetical; nowhere did he say that anybody had actually done it. Moreover, it was presented as a hypothetical with the introduction "Can you see the massive bug in this piece of code?"


Log in to reply