Are Database Triggers Evil

Are database triggers evil?

The main problems with triggers are

• They are completely Global - they apply no matter what the context of the table activity;
• They are stealthy; it's easy to forget they are there until they hurt you with unintended (and very mysterious) consequences.

This just means they need to be carefully used for the proper circumstances; which in my experience is limited to relational integrity issues (sometimes with finer granularity than you can get declaratively); and usually not for business or transactional purposes. YMMV.

When are database triggers bad?

I think they're OK when they are used to populate a separate, one-off set of tables for things like logging, aggregation etc. for security or creating metadata for example.

When you start altering your "live" data or "looping back" into your biz info tables, that's when they become evil and unwieldy. They are also utterly unnecessary for this. There is nothing that a trigger does that a stored proc cannot do.

I feel like they are SQL's evil equivalent to GOTOs in programming languages. Legal, but to be avoided unless absolutely necessary, and they are NEVER absolutely necessary.

If database triggers are evil, is it also evil to have side effects when setting a property in java or C#?

Going against the grain here...

Properties should NOT trigger side effects. That's what Method's are for.

By having properties cause side effects you end up in a situation were code is essentially hidden. People rarely expect properties to kick off some process or cause something else to flip. If this has to be documented, then its not obvious and subject to being ignored.

However, we do expect something to happen when we call a Method.

Taking @astander's example, he says that the act of changing "Price" should cause a different property "Cost" to change. However, what if we later add a new property called "Discount"? The code around the Price and Amount properties would have to change. Which isn't very discoverable.

However, if Cost calculated itself.. then everything is better off.

Are database triggers safe for cross table integrity constraints?

The answer is triggers are not safe.

It turns out that the trigger really doesn't see uncommited changes done in other transactions and passes without an error. It can be demonstrated like this

Transaction 1:

START TRANSACTION;
INSERT INTO plays_in (BetID, PlayerID) VALUES (1,1); -- query A

Transaction 2:

START TRANSACTION;
INSERT INTO plays_in (BetID, PlayerID) VALUES (1,2); -- query B; in conflict with A, but passses

Both transactions:

COMMIT;

Now plays_in will contains both inserted records even though if A and B were executed in a single transaction, the trigger would throw an error.

The whole example sources can be obained here

Reasons for objection to SQL triggers that insert data into other tables?

If you are careful with your trigger code, there is nothing inherently bad about it. Some people get bitten by bad trigger code and then decide that triggers are bad (eventhough it was the bad trigger code that was the problem). They then generalize this as, "never use triggers".

The other problem is....

Using the audit tables as an example, suppose you have a stored procedure that updates a table AND puts data in to an audit table. Now suppose you write trigger code to put data in to the audit table. You could end up with duplicate audit data.

Is a 'blackhole' table evil?

I don't think blackhole has any real pros.

Writing the trigger code to move data around is probably not noticably less work than writing the code to insert the data in the right place in the first place.

As Christian Oudard writes, it doesn't reduce complexity - just moves it to a place where it's really hard to debug.

On the downside:

"Side effects" are usually a bad idea in software development. Triggers are side effects - I intend to do one thing (insert data in a table), and it actually does lots of other things. Now, when I'm debugging my code, I have to keep all the side effects in my head too - and the side effects could themselves have side effects.

most software spends far more time in maintenance than it does in development. Bringing new developers into the team and explaining the black hole trick is likely to increase the learning curve - for negligible benefit (in my view).

Because triggers are side effects, and it's relatively easy to set off a huge cascade of triggers if you're not careful, I've always tried to design my databases without a reliance on triggers; where triggers are clearly the right way to go, I've only let my most experienced developers create them. The black hole trick makes triggers into a normal, regular way of working. This is a personal point of view, of course.

Should mysql triggers happen on the application level or the db level?

Right now I'm doing this in the application level

That's usually best.

Code belongs in the application.

Data belongs in the database.

Code does not belong in the database, any more than data belongs in the application code.

Truggers are generally a very bad idea because they fragment the processing logic into two places -- the application and the database.

It's often best to have the application logic in one place. Outside the database.

The data is the most valuable part of the application. Don't pollute it with triggers or other bits of application logic.

[This answer will be downvoted by folks who like stored procedures and triggers in the database.]

Should I use a database trigger for the following problem?

You can do this using SQL Job. Write Stored Procedure to copy data and put in schedule job. This job will run after every 5 minutes.

step_by_step_guide_to_add_a_sql_job_in_sql_server_2005

sql_server_agent_jobs

While updating table1, how do I INSERT to table2 for every change in table 1?

Use the output clause instead of trigger, they are bad.

You need the condition "where data_old <> data_new" case if you updated a column with the same value, SQL Server marked it as changed, even if the value hasn't changed

create table #example (id int identity(1,1) not null, data nvarchar(max));
insert into #example (data) values ('value 1'),('value 2'), ('value 3');

create table #audit  (id int, data_old nvarchar(max), data_new nvarchar(max), [When] datetime not null default (getdate()));

insert into #audit (id, data_old, data_new) 
select id, data_old, data_new
from (
    update #example 
    set data = 'value changed' 
    output inserted.id, deleted.data as data_old, inserted.data as data_new
    where id = 2
)changed (id, data_old, data_new)
where data_old <> data_new
select * from #audit

will result with this in #audit :

---

Related Topics