Alternative to triggers in SQL Server

Question

To eliminate the potential problems with triggers, what are some of the alternatives one may use to get the same functionality of reacting to an event fired on a INSERT action?

I have a database that needs to have some additional values added on insert. The INSERT is controlled by compiled code and cannot be changed.

EXAMPLE: The program inserts a string and from this string I need to supply an integer to a new field that points to a look-up table.

If there is an alternative to a trigger then please let me know some pros and cons to any alternative. The main reason for this is that Triggers are not allowed in our DB standards.

SQL Server 2008 Enterprise

Answer 1

Can you create a stored procedure to handle everything for you?

BEGIN
 -- DO YOUR INSERT CODE

  IF @@ERROR = 0
    -- DO THE OTHER STUFF?

Or you could do it within your application. Try the insert, if it works, then do the other steps.

Answer 2

Your options are limited here. I think your only other alternative is to do your inserts via a stored procedure call and put the extra code in the stored procedure.

Answer 3

Alternatives to plain-old inserts can be done using stored procedures, triggers, or more complicated insert statements. Since you have no control over the insert statements, you won't be able to use stored procedures, either. So your only option is triggers.

What you're describing is precisely why triggers exist. If you need to accomplish this task then it can't be done under the constraints you've listed.

Triggers are the best option. Get the DB standards changed (or at least allow this task to be an exception) because they are flawed.

Answer 4

Triggers are the way to perform an action after an event (insert, update, delete) occurs on a SQL table; the fact that they exist makes it unlikely that there's any tenable alternative. It's unfortunate to say, but the DB standards you say are in place effectively prevent you from doing what you want without having some process running that periodically watches your table and then performs the action you need it to, or changing all your database CrUD operations to go through stored procedures which do what you want them to. Since you say that the latter isn't possible -- you can't change the INSERT statements -- then you're left with just triggers.

Answer 5

How do you determine your integer, based on the string being inserted?

One alternative you might want to look into are computed columns in SQL Server. If that matching is a pretty straightforward one (e.g. extract the character 10 through 14 from the string) or something like that, you could create a computed column to do so automagically - no trigger needed.

You can even make those computed columns persisted (physically stored as part of your tables) and create indices on these fields!

Computed columns are available from SQL Server 2000 on, persisted columns from SQL Server 2005.

Answer 6

SQL Server 2005 now has something called an OUTPUT clause that can do additional processing after an INSERT (or other action) occurs. This article covers more of the details. For instance, if you need to do processing after an INSERT command, you could do something like...

INSERT INTO Contact
(FirstName, MiddleName, LastName)

OUTPUT INSERTED.ContactID, INSERTED.FirstName, INSERTED.MiddleName, INSERTED.LastName
INTO Contact_Audit    
VALUES
(@@SCOPTE_IDENTITY, 'Joe', 'D.', 'Schmoe')

And you'd have your uniquely created ID for them available.