Alternative to triggers in SQL Server_问答_开发者

To eliminate the potential problems with triggers, what are some of the alternatives one may use 开发者_如何学Goto 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

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.

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.

I know that was asked a long time ago. With SQL Server 2008 "Change Data Capture" MSDN was introduced. Another alternative, but only valid after 2008 R2 is "Change Tracking" Setting up change tracking. While you can query rows to filter (look here) what was changed, this may or may not "resolve" the issues with triggers.

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.

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
(@@SCOPE_IDENTITY, 'Joe', 'D.', 'Schmoe')

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

If you want to use data history then go with system version history tables. you don't need to create trigger explicitly.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

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.

I think we can implement trigger with hibernate event system nowdays despite of the performance impact.I didn't do that before.but I think it works