q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there i
s
any workaround.
Thanks,Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case yo
u
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Fri, 29 Dec 2006 10:19:01 -0800, JIM.H. wrote:

>Hi,
>I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
>rollback record in the table?
Hi Jim,
It definitely does not. Run the following code in Sql Server Management
Studio or Query Analyzer to see for yourself:
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY);
go
CREATE TRIGGER TestTrig
ON Test AFTER INSERT
AS
RAISERROR('This is a test', 16, 1);
go
INSERT INTO Test (a)
VALUES (1);
SELECT *
FROM Test;
go
DROP TABLE Test;
go

> It seems it does and I am wondering if there is
>any workaround.
Is it possible that your client automatically requests a rollback upon
seeing the error condition? I know some clients (Access, for instance)
that make a habit of rolling back first and asking questions later.
Hugo Kornelis, SQL Server MVP|||JIM.H. wrote:
> Hi Paul,
> Thanks you very much, no try check, here is the code:
> -- duplicate record check
> IF EXISTS (
> SELECT *
> FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
> WHERE i.tID=@.tID
> )
> Begin
> SET @.ErrorD = 'Dup Record'
> RAISERROR(@.ErrorD, 16, 1)
> Return
> End
>
>
Unfortunately you only posted part of the trigger code but this looks
suspiciously like your code is at fault. I suspect your trigger fails
to operate correctly when multiple rows are updated because you don't
appear to be referencing the INSERTED table properly. That might
explain some unpredictable behaviour.
If you want help to rewrite the trigger we'll need a better spec - read
my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--