Posted by Hugo Kornelis on 10/22/06 22:06

On 19 Oct 2006 10:02:06 -0700, steven@bluemania.co.uk wrote:

>Does anyone know how to do the following. I'm trying to mimic
>replication with triggers.

On 19 Oct 2006 10:54:16 -0700, samh wrote:

> Replication is not possible, the client this is
>being developed for is insisting on not using it so I don't have much
>choice.

Hi Steve,

Not an answer to the question, but solid advice nonetheless: make sure
to ask the client for his/her/their motives. As a consultant, you are
supposed to take the client serious, but you are also supposed to know
more about your subject than they do. They may have very valid motives,
but they also might be making their decision on urban legend, or on a
bug that has long been fixed. Ask them why replication is out of the
question, than decide whether to agree with them and roll your own, or
to explain to them why they should reconsider.

(snip)
>What I was hoping for was someway of saying, "I'm inserting from my
>application so don't do the trigger".

Many years ago, we faced a similar issue when upgrading from SQL Server
4.2 (if memory serves) to 6.5. We used @@PROCID in the triggers to check
if the DML statement was executed in a stored procedure, and if so in
which sp. With 6.5, behaviour of @@PROCID changed - instead of the id of
the stored procedure that held the INSERT, UPDATE or DELETE statement,
it now held the id of the trigger itself. When we filed this as a bug,
we were brushed of by Microsoft. They claimed that the behaviour was as
intended, and when we pointed them to the documentation (that still
described the 4.2 behaviour), they claimed that the docs were wrong.

Since the workarounds supplied by MS didn't work either, we eventually
came up with our own kludge - we added a dummy column to the tables; in
stored procs that were supposed not to fire the trigger, we set the
value of this column to a dummy value. And in the FOR UPDATE trigger, we
included
IF UPDATE(DummyColumn) RETURN

Of course, this was only safe because all code was under our control. If
an end user would have had the ability to issue an UPDATE with the dummy
coolumn included in the SET clause, disaster would have followed. Also,
we were lucky only to need this in UPDATE triggers.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]