Is Oracle better than Sql Server in terms of Trigger functionality?

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Hi Eric, As far as I know, Sql Server does not have a concept of "before" Trigger. ie Trigger being fired before the DML statement. Oracle has got this feature. Sql Server has got "after" triggers only. Sql Server 2000 introduced a new feature "Instead of" Triggers which is similiar to "Before" Triggers but not exactly the same it seems. I am working in Sql Server 7.0 for the past 2 years. We used to manage with stored procedures and triggers for all sorts of validations. We never encountered the usage of "Before" triggers. Whatever be the validations necessary before firing a DML statement, we will put in a stored procedure. Our triggers will have further validations. I think in Oracle, they used to write all validations in "Before" triggers and "After" triggers only and not in stored procedures. As far As I know trigger gives more performance benefits compared to stored procedures. Is this the product deficiency of Sql server? Can u tell me what is the great thing abt this "Before" trigger or can we manage to create database applications with good performance without this feature? Thanks in advance. rgds Shankar

-- Anonymous, November 28, 2000

Answers

Shankar,

I checked the usenet forums, the Oracle website, and a colleague who has been an Oracle DBA. None of these sources provides an example of what types of operations have a real requirement for a "Before" trigger.

You can certainly manage database applications with good performance without this feature.

Hope this helps,

Eric

-- Anonymous, December 01, 2000


shankar, this may not be the right answer to your question but i think you cannot compare stored procedures and triggers as far as performance goes. Stored procedures can be called from triggers and triggers fire automatically where a perticular event oocurs. For oracle there were(in pre oracle8 era)12 different type of triggers. classification is, before insert/update/delete -statement level/row level after insert/update/delete -statement level/row level.

In oracle 8/8i they introduced some more triggers which are system level as well as 'instead of' triggers. System level triggers are sort of like fire when database started/shutdown etc. and instead of is the one fires when you want to do something intead of the default behaviour, they normally are used for views. ie. you can write them for a view. I hope this gives some insight to oracle side of your question.

-- Anonymous, January 08, 2001


One use for a Before Trigger would be to set fields in a row such as the last_update_date and last_user_id. These can be set by a stored procedure but that is not as secure as a trigger. I have implemented this functionality by doing an update in the trigger but the system was doing two updates.

-- Anonymous, January 11, 2001

Moderation questions? read the FAQ