J - For comparison information check out
http://www.microsoft.com/sql/productinfo/compare.htm . SQL Server triggers
are post-modification (they are part of the transaction itself - meaning you
can rollback the transaction within the trigger). You'll want to do your
data validation prior to the transaction via constraints, rules, defaults,
nullability of the column, the datatype and possibly (if not from the
others) via stored procedures prior to the transaction. SQL Server gives
you the capability to autoincrement a field either using an IDENTITY
property on an integer field or in v7 by using a GUID of the
uniqueidentifier datatype wiht a default of NEWID(). For the detecting
changed values, SQL Server provides an IF UPDATE clause to the update
trigger to test if a column has been updated (in addition you can utilize
the INSERTED and DELETED logical tables (basically views to the log) to view
what modifications have taken place).
Quote:> I received these critiques of MS SQL Server from an Oracle devotee doing
> appraisal for a client of merits of various db systems. They seem valid
> having never used Oracle, I am not really in position to compare the two.
> What would be the practical advantages/disadvantages of SQL vs Oracle,
> beside such things as TCO, interface etc. Or are there any whitepapers,
> articles comparisons recommended.
> Triggers. SQL Server does not have the ability to trigger both before and
> after an action has taken place. It can only trigger after an action. This
> means that for many actions, code must be written back at the client end
> rather than a trigger performing the action at the server. In turn this
> means a lot more coding, and much slower performance, and de centralised
> business logic.
> Generators or Sequences This concept does not exist in SQL Server. Instead
> they use Auto Incrementing Columns called Identities. Although this is
> acceptable for low end products like access, it is not for a corporate
> database scenario. There are many negative consequences of this technology
> Stored Procedure Language and nesting SP's into another DML statement.
> is not possible in SQL 7 because the SQL7 SP language forces at least 3
> steps. Provide input parameters, Execute, Select the output values. An
> example of how this effects development is that batch inserts using stored
> procedure outputs are not possible. Eg insert into T1 select MyProcedure,
> Column1, Column2 from T2 is not possible.
> Once again this can be worked around, but the result is always a
> hit, extra coding, and decentralisation of the business logic.
> Detecting changed values in specific columns in an Update Trigger
> With SQL Server this is done by coding bit evaluation arithmetic based on
> the column position. Not only is this somewhat clumsy, but because it is
> based on the column absolute position, any table changes must be followed
> re development of the stored procedure library.