tag:blogger.com,1999:blog-902486075140485537.post4779799998556714889..comments2023-09-15T16:47:49.946+01:00Comments on Piotr Rodak:<br> if datepart(dw, getdate()) in (6, 7) use pubs;: INSTEAD OF triggers on viewsPiotr Rodakhttp://www.blogger.com/profile/08418009093976236989noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-902486075140485537.post-627601686444842662010-07-22T08:45:40.074+01:002010-07-22T08:45:40.074+01:00I've used INSTEAD OF triggers for specific pur...I've used INSTEAD OF triggers for specific purposes - mainly for ruby-on-rails-style db<>app interfaces. A big plus over sprocs is that they handle set-based database operations coming in from the app.<br /><br />The point about dropping out of the trigger if there is nothing to do is very important for performance reasons. Three other points are: (1) triggers receive two ROWSETS of data ([inserted] table and [deleted] table), so all operations inside the trigger must cater for this (i.e. be set-based or loop through the rows), (2) There may still be triggers on the individual tables that are affected, and these will be fired, (3) Obviously you can call sprocs from the trigger.<br /><br />As others have implied, keeping one set of business logic when multiple end-user systems are in place, often means that, in practice, it sits with the database. How do folks feel about this?Rob McCleanhttps://www.blogger.com/profile/16073973735463038450noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-54148110253194122432010-07-18T13:29:25.971+01:002010-07-18T13:29:25.971+01:00Good point.
I always try to follow the principle...Good point. <br /><br />I always try to follow the principle of not giving applications direct access to base tables. Ideally data interfaces should be via sprocs but for some OR mappers there can be issues with sprocs. In these cases I try to encourage the use of views. With 'instead of triggers' you can then refactor/change base tables whilst maintaing the data interface offered by the view.Michael Baylonhttp://michaelbaylon.wordpress.com/noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-7813574807817202272010-07-16T19:22:10.927+01:002010-07-16T19:22:10.927+01:00I agree. Views are a fantastic way of decoupling i...I agree. Views are a fantastic way of decoupling implementation from the higher levels of application. As Adam Machanic says, database should be regarded as data interface rather than data store. That's why I am opposing to use OR mappers wherever possible, they have nasty habit of relying on bare tables.Piotr Rodakhttps://www.blogger.com/profile/08418009093976236989noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-59014820266495489382010-07-16T16:18:05.882+01:002010-07-16T16:18:05.882+01:00Nice post.
You're right, it is a feature that...Nice post.<br /><br />You're right, it is a feature that you don't see being used that often. <br /><br />They can be really useful when wanting to refactor/change your database design eg base tables - whilst still wanting to maintain an existing data contract to external contracts via views.Michael Baylonhttp://michaelbaylon.wordpress.com/noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-57238547072438335502010-07-14T19:09:26.340+01:002010-07-14T19:09:26.340+01:00Thanks Dewraj :)Thanks Dewraj :)Piotr Rodakhttps://www.blogger.com/profile/08418009093976236989noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-25714961178162647362010-07-14T18:53:51.198+01:002010-07-14T18:53:51.198+01:00Hi Piotr,
Nice one, simple and insteresting!
Tha...Hi Piotr,<br /><br />Nice one, simple and insteresting!<br /><br />Thanks<br />DewrajDewrajnoreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-46946423261481985552010-07-07T20:15:23.422+01:002010-07-07T20:15:23.422+01:00Thanks Adam, I sent you email :)Thanks Adam, I sent you email :)Piotr Rodakhttps://www.blogger.com/profile/08418009093976236989noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-66357942282806053372010-07-07T17:47:48.444+01:002010-07-07T17:47:48.444+01:00Hi Piotr,
Nice post!
Can you send me an e-mail w...Hi Piotr,<br /><br />Nice post!<br /><br />Can you send me an e-mail when you get a chance? adam [at] sqlblog [dot] com<br /><br /><br />Thanks!<br />Adam MachanicAdam Machanichttp://sqlblog.comnoreply@blogger.com