Sql-server – How to prevent a trigger from running on certain statements

MySQLsql servertrigger

I'm looking for an answer that satisfies my need on both SQL Server and MySQL.

I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations where I would like to bypass those triggers. I know that I can make a column on the tables and use it as a sort of flag for the trigger, but I'm hoping to find something that generates less clutter.

Is it possible to prevent a trigger from being called using a variable in my statement or some other query side thing?

EDIT: To clarify, I'm not expecting an actual trigger/query that works on both platforms. I'm looking for a solution that fits the desired goal of not having to use an extra column in each table.

Best Answer

I answered a question like this back on Jan 20, 2012 : Disable trigger for just one table

It was a workaround for MySQL I suggested. No negative feedback so far.

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.

PostgreSQL pl/perl trigger, differentiate null vs empty

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}

Best Answer

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

PostgreSQL pl/perl trigger, differentiate null vs empty

Related Question