Trigger logic - this ok

microsoft.public.sqlserver.programming

10 Sep 2006 12:24 AM

Rob

If I want the row inserted into the table regardless of whether or not the
Stored proc completes successfully, then is there any problem with employing
the following technique (placing a "commit transaction" on the first line)
?

CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT, UPDATE, DELETE
AS
commit transaction

DECLARE @OrderID VARCHAR(20)
SELECT @OrderID = OrderNumber
FROM Inserted

BEGIN
EXEC prProcessOrder @OrderID
END

10 Sep 2006 1:20 AM

Rob

Actually, looks like the commit removes the Inserted memory variable....
thus I am now asking...
CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT, UPDATE, DELETE
AS

DECLARE @OrderID VARCHAR(20)
SELECT @OrderID = OrderNumber
FROM Inserted

commit transaction

BEGIN
EXEC prProcessOrder @OrderID
END

Show quote

"Rob" <rwch***@comcast.net> wrote in message
news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
> If I want the row inserted into the table regardless of whether or not the
> Stored proc completes successfully, then is there any problem with
> employing the following technique (placing a "commit transaction" on the
> first line) ?
>
>
> CREATE TRIGGER testtrigger ON dbo.Orders
> FOR INSERT, UPDATE, DELETE
> AS
> commit transaction
>
> DECLARE @OrderID VARCHAR(20)
> SELECT @OrderID = OrderNumber
> FROM Inserted
>
> BEGIN
> EXEC prProcessOrder @OrderID
> END
>
>
>

10 Sep 2006 8:03 AM

John Bell

Hi Rob

Commiting the transaction in the trigger may be ok if you only have one
stored procedure to update the table, but if the table gets updated in a
multitude of situations there may be a situation where you are prematurely
commiting a transaction and therefore not obeying the ACID rules.

If you want to take it out of the transaction then you could use
xp_cmdshell, but this would be relatively slow.

You procedure is not coded to cater for multiple rows being updated and is
therefore fundamentally flawed.

John

Show quote

"Rob" wrote:

> Actually, looks like the commit removes the Inserted memory variable....
> thus I am now asking...
> CREATE TRIGGER testtrigger ON dbo.Orders
> FOR INSERT, UPDATE, DELETE
> AS
>
> DECLARE @OrderID VARCHAR(20)
>   SELECT @OrderID = OrderNumber
>   FROM Inserted
>
> commit transaction
>
>
> BEGIN
>      EXEC prProcessOrder @OrderID
>   END
>
>
> "Rob" <rwch***@comcast.net> wrote in message
> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
> > If I want the row inserted into the table regardless of whether or not the
> > Stored proc completes successfully, then is there any problem with
> > employing the following technique (placing a "commit transaction" on the
> > first line) ?
> >
> >
> > CREATE TRIGGER testtrigger ON dbo.Orders
> > FOR INSERT, UPDATE, DELETE
> > AS
> > commit transaction
> >
> > DECLARE @OrderID VARCHAR(20)
> > SELECT @OrderID = OrderNumber
> > FROM Inserted
> >
> > BEGIN
> >     EXEC prProcessOrder @OrderID
> > END
> >
> >
> >
>
>
>

11 Sep 2006 3:39 PM

Rob

Hi John,

I do not really like doing this.   I know it goes against the grain.... but
I am told that they want to commit the insert regardless of whether the sp
runs ok...

Some more info...

The insert into the table is an isolated insert (not part of a larger
transaction). Also, only 1 row is added at a time (no bulk inserts).

The question I have, and I want to be sure on this is....

Does the "commit transaction" in the code below apply soley to committing
the Insert into the "Orders" table.   Can it cause other issues ?

CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT AS

DECLARE @OrderID VARCHAR(20)
   SELECT @OrderID = OrderNumber
   FROM Inserted

commit transaction

BEGIN
      EXEC prProcessOrder @OrderID
   END

Thanks,
Rob

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com...
> Hi Rob
>
> Commiting the transaction in the trigger may be ok if you only have one
> stored procedure to update the table, but if the table gets updated in a
> multitude of situations there may be a situation where you are prematurely
> commiting a transaction and therefore not obeying the ACID rules.
>
> If you want to take it out of the transaction then you could use
> xp_cmdshell, but this would be relatively slow.
>
> You procedure is not coded to cater for multiple rows being updated and is
> therefore fundamentally flawed.
>
> John
>
> "Rob" wrote:
>
>> Actually, looks like the commit removes the Inserted memory variable....
>> thus I am now asking...
>> CREATE TRIGGER testtrigger ON dbo.Orders
>> FOR INSERT, UPDATE, DELETE
>> AS
>>
>> DECLARE @OrderID VARCHAR(20)
>>   SELECT @OrderID = OrderNumber
>>   FROM Inserted
>>
>> commit transaction
>>
>>
>> BEGIN
>>      EXEC prProcessOrder @OrderID
>>   END
>>
>>
>> "Rob" <rwch***@comcast.net> wrote in message
>> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
>> > If I want the row inserted into the table regardless of whether or not
>> > the
>> > Stored proc completes successfully, then is there any problem with
>> > employing the following technique (placing a "commit transaction" on
>> > the
>> > first line) ?
>> >
>> >
>> > CREATE TRIGGER testtrigger ON dbo.Orders
>> > FOR INSERT, UPDATE, DELETE
>> > AS
>> > commit transaction
>> >
>> > DECLARE @OrderID VARCHAR(20)
>> > SELECT @OrderID = OrderNumber
>> > FROM Inserted
>> >
>> > BEGIN
>> >     EXEC prProcessOrder @OrderID
>> > END
>> >
>> >
>> >
>>
>>
>>

11 Sep 2006 5:14 PM

John Bell

Hi Rob

As Erland has stated it will fail silently in SQL 2000 therefore it is a
non-starter.

You should never assume that you will always only insert/delete/update
single rows a table; the requirements in the future may change and you may
not be there, or may not be aware of the intention to make the change that
results in multiple rows being updated, leading to inconsistent/corrupted
data.

John

Show quote

"Rob" wrote:

> Hi John,
>
> I do not really like doing this.   I know it goes against the grain.... but
> I am told that they want to commit the insert regardless of whether the sp
> runs ok...
>
> Some more info...
>
> The insert into the table is an isolated insert (not part of a larger
> transaction). Also, only 1 row is added at a time (no bulk inserts).
>
> The question I have, and I want to be sure on this is....
>
> Does the "commit transaction" in the code below apply soley to committing
> the Insert into the "Orders" table.   Can it cause other issues ?
>
> CREATE TRIGGER testtrigger ON dbo.Orders
> FOR INSERT AS
>
> DECLARE @OrderID VARCHAR(20)
>    SELECT @OrderID = OrderNumber
>    FROM Inserted
>
> commit transaction
>
>
> BEGIN
>       EXEC prProcessOrder @OrderID
>    END
>
> Thanks,
> Rob
>
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com...
> > Hi Rob
> >
> > Commiting the transaction in the trigger may be ok if you only have one
> > stored procedure to update the table, but if the table gets updated in a
> > multitude of situations there may be a situation where you are prematurely
> > commiting a transaction and therefore not obeying the ACID rules.
> >
> > If you want to take it out of the transaction then you could use
> > xp_cmdshell, but this would be relatively slow.
> >
> > You procedure is not coded to cater for multiple rows being updated and is
> > therefore fundamentally flawed.
> >
> > John
> >
> > "Rob" wrote:
> >
> >> Actually, looks like the commit removes the Inserted memory variable....
> >> thus I am now asking...
> >> CREATE TRIGGER testtrigger ON dbo.Orders
> >> FOR INSERT, UPDATE, DELETE
> >> AS
> >>
> >> DECLARE @OrderID VARCHAR(20)
> >>   SELECT @OrderID = OrderNumber
> >>   FROM Inserted
> >>
> >> commit transaction
> >>
> >>
> >> BEGIN
> >>      EXEC prProcessOrder @OrderID
> >>   END
> >>
> >>
> >> "Rob" <rwch***@comcast.net> wrote in message
> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
> >> > If I want the row inserted into the table regardless of whether or not
> >> > the
> >> > Stored proc completes successfully, then is there any problem with
> >> > employing the following technique (placing a "commit transaction" on
> >> > the
> >> > first line) ?
> >> >
> >> >
> >> > CREATE TRIGGER testtrigger ON dbo.Orders
> >> > FOR INSERT, UPDATE, DELETE
> >> > AS
> >> > commit transaction
> >> >
> >> > DECLARE @OrderID VARCHAR(20)
> >> > SELECT @OrderID = OrderNumber
> >> > FROM Inserted
> >> >
> >> > BEGIN
> >> >     EXEC prProcessOrder @OrderID
> >> > END
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

11 Sep 2006 5:50 PM

Rob

Hi John,

When I tested this it did appear to "work"... I do know it is very ugly.
They are wanting to do this on a temporary basis...

If you insert an OrderNumber < 6 characters and a valid date, the
transactions sticks, even if the sp fails... sample below...

insert into Orders select '1111','1/1/2006'

Thanks,
Rob

CREATE TABLE [dbo].[Orders] (
[OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL ,
[Shipped] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrdersLog] (
[OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL ,
[Shipped] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrdersRaw] (
[Ord] [char] (6) COLLATE Latin1_General_BIN NULL ,
[DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrdersSuccessFail] (
[OrderId] [char] (6) COLLATE Latin1_General_BIN NULL ,
[SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderNumber]
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT
AS

DECLARE @OrderID VARCHAR(20)
SELECT @OrderID = OrderNumber
FROM Inserted

COMMIT   TRANSACTION

BEGIN TRANSACTION
insert into OrdersSuccessFail select @OrderID, 'Fail'
COMMIT   TRANSACTION

BEGIN
     EXEC prProcessOrder @OrderID
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS

declare @ErrorCode int
Select @ErrorCode = @@Error

-- The following code causes failure due to incorrect datatype
If @ErrorCode = 0
Begin
   Insert into OrdersLog select @OrderId, 'mmm'
   Select @ErrorCode = @@Error
End

If @ErrorCode = 0
begin

update OrdersSuccessFail set SuccessFail = 'Success' where OrderId =
@OrderId

end

GO

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:5ED03271-A943-4BF3-AC38-4A02FA7A2EEF@microsoft.com...
> Hi Rob
>
> As Erland has stated it will fail silently in SQL 2000 therefore it is a
> non-starter.
>
> You should never assume that you will always only insert/delete/update
> single rows a table; the requirements in the future may change and you may
> not be there, or may not be aware of the intention to make the change that
> results in multiple rows being updated, leading to inconsistent/corrupted
> data.
>
> John
>
> "Rob" wrote:
>
>> Hi John,
>>
>> I do not really like doing this.   I know it goes against the grain....
>> but
>> I am told that they want to commit the insert regardless of whether the
>> sp
>> runs ok...
>>
>> Some more info...
>>
>> The insert into the table is an isolated insert (not part of a larger
>> transaction). Also, only 1 row is added at a time (no bulk inserts).
>>
>> The question I have, and I want to be sure on this is....
>>
>> Does the "commit transaction" in the code below apply soley to
>> committing
>> the Insert into the "Orders" table.   Can it cause other issues ?
>>
>> CREATE TRIGGER testtrigger ON dbo.Orders
>> FOR INSERT AS
>>
>> DECLARE @OrderID VARCHAR(20)
>>    SELECT @OrderID = OrderNumber
>>    FROM Inserted
>>
>> commit transaction
>>
>>
>> BEGIN
>>       EXEC prProcessOrder @OrderID
>>    END
>>
>> Thanks,
>> Rob
>>
>>
>>
>>
>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com...
>> > Hi Rob
>> >
>> > Commiting the transaction in the trigger may be ok if you only have one
>> > stored procedure to update the table, but if the table gets updated in
>> > a
>> > multitude of situations there may be a situation where you are
>> > prematurely
>> > commiting a transaction and therefore not obeying the ACID rules.
>> >
>> > If you want to take it out of the transaction then you could use
>> > xp_cmdshell, but this would be relatively slow.
>> >
>> > You procedure is not coded to cater for multiple rows being updated and
>> > is
>> > therefore fundamentally flawed.
>> >
>> > John
>> >
>> > "Rob" wrote:
>> >
>> >> Actually, looks like the commit removes the Inserted memory
>> >> variable....
>> >> thus I am now asking...
>> >> CREATE TRIGGER testtrigger ON dbo.Orders
>> >> FOR INSERT, UPDATE, DELETE
>> >> AS
>> >>
>> >> DECLARE @OrderID VARCHAR(20)
>> >>   SELECT @OrderID = OrderNumber
>> >>   FROM Inserted
>> >>
>> >> commit transaction
>> >>
>> >>
>> >> BEGIN
>> >>      EXEC prProcessOrder @OrderID
>> >>   END
>> >>
>> >>
>> >> "Rob" <rwch***@comcast.net> wrote in message
>> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
>> >> > If I want the row inserted into the table regardless of whether or
>> >> > not
>> >> > the
>> >> > Stored proc completes successfully, then is there any problem with
>> >> > employing the following technique (placing a "commit transaction"
>> >> > on
>> >> > the
>> >> > first line) ?
>> >> >
>> >> >
>> >> > CREATE TRIGGER testtrigger ON dbo.Orders
>> >> > FOR INSERT, UPDATE, DELETE
>> >> > AS
>> >> > commit transaction
>> >> >
>> >> > DECLARE @OrderID VARCHAR(20)
>> >> > SELECT @OrderID = OrderNumber
>> >> > FROM Inserted
>> >> >
>> >> > BEGIN
>> >> >     EXEC prProcessOrder @OrderID
>> >> > END
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

12 Sep 2006 7:41 AM

John Bell

Hi Rob

Your error handling is very poor read
http://www.sommarskog.se/error-handling-II.html on how/what you should be
checking

For instance:

declare @ErrorCode int
Select @ErrorCode = @@Error

-- The following code causes failure due to incorrect datatype
If @ErrorCode = 0

Seems a total waste of time as @ErrorCode will always be 0 after the
declaration.

You datatypes are not consistent between tables which leads to errors such as
"Server: Msg 8152, Level 16, State 9, Procedure prProcessOrder, Line 9
String or binary data would be truncated."

After sorting this out you will not get the entry in the log that you
require if you did something like:
Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912'
SELECT * FROM ORDERS
SELECT * FROM ORDERSLOG
SELECT * FROM [dbo].[OrdersSuccessFail]

Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912'
/*
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate key
in object 'Orders'.
The statement has been terminated.
*/
SELECT * FROM ORDERS
SELECT * FROM ORDERSLOG
SELECT * FROM [dbo].[OrdersSuccessFail]
/* Nothing has been logged */

Erlands point about this failing on SQL 2005 may also indicate that the
behavour of this on SQL 2000 may not be guaranteed in future patches/hotfixes

John

Show quote

"Rob" wrote:

> Hi John,
>
> When I tested this it did appear to "work"... I do know it is very ugly.
> They are wanting to do this on a temporary basis...
>
> If you insert an OrderNumber < 6 characters and a valid date, the
> transactions sticks, even if the sp fails... sample below...
>
> insert into Orders select '1111','1/1/2006'
>
> Thanks,
> Rob
>
>
> CREATE TABLE [dbo].[Orders] (
> [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL ,
> [Shipped] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[OrdersLog] (
> [OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL ,
> [Shipped] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[OrdersRaw] (
> [Ord] [char] (6) COLLATE Latin1_General_BIN NULL ,
> [DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[OrdersSuccessFail] (
> [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL ,
> [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Orders] ADD
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
>   [OrderNumber]
> ) ON [PRIMARY]
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE TRIGGER testtrigger ON dbo.Orders
> FOR INSERT
> AS
>
> DECLARE @OrderID VARCHAR(20)
>   SELECT @OrderID = OrderNumber
>   FROM Inserted
>
> COMMIT   TRANSACTION
>
>
> BEGIN TRANSACTION
> insert into OrdersSuccessFail select @OrderID, 'Fail'
> COMMIT   TRANSACTION
>
> BEGIN
>      EXEC prProcessOrder @OrderID
>   END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS
>
> declare @ErrorCode int
> Select @ErrorCode = @@Error
>
> -- The following code causes failure due to incorrect datatype
> If @ErrorCode = 0
> Begin
>    Insert into OrdersLog select @OrderId, 'mmm'
>    Select @ErrorCode = @@Error
> End
>
> If @ErrorCode = 0
> begin
>
>   update OrdersSuccessFail set SuccessFail = 'Success' where OrderId =
> @OrderId
>
> end
>
> GO
>
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:5ED03271-A943-4BF3-AC38-4A02FA7A2EEF@microsoft.com...
> > Hi Rob
> >
> > As Erland has stated it will fail silently in SQL 2000 therefore it is a
> > non-starter.
> >
> > You should never assume that you will always only insert/delete/update
> > single rows a table; the requirements in the future may change and you may
> > not be there, or may not be aware of the intention to make the change that
> > results in multiple rows being updated, leading to inconsistent/corrupted
> > data.
> >
> > John
> >
> > "Rob" wrote:
> >
> >> Hi John,
> >>
> >> I do not really like doing this.   I know it goes against the grain....
> >> but
> >> I am told that they want to commit the insert regardless of whether the
> >> sp
> >> runs ok...
> >>
> >> Some more info...
> >>
> >> The insert into the table is an isolated insert (not part of a larger
> >> transaction). Also, only 1 row is added at a time (no bulk inserts).
> >>
> >> The question I have, and I want to be sure on this is....
> >>
> >> Does the "commit transaction" in the code below apply soley to
> >> committing
> >> the Insert into the "Orders" table.   Can it cause other issues ?
> >>
> >> CREATE TRIGGER testtrigger ON dbo.Orders
> >> FOR INSERT AS
> >>
> >> DECLARE @OrderID VARCHAR(20)
> >>    SELECT @OrderID = OrderNumber
> >>    FROM Inserted
> >>
> >> commit transaction
> >>
> >>
> >> BEGIN
> >>       EXEC prProcessOrder @OrderID
> >>    END
> >>
> >> Thanks,
> >> Rob
> >>
> >>
> >>
> >>
> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> >> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com...
> >> > Hi Rob
> >> >
> >> > Commiting the transaction in the trigger may be ok if you only have one
> >> > stored procedure to update the table, but if the table gets updated in
> >> > a
> >> > multitude of situations there may be a situation where you are
> >> > prematurely
> >> > commiting a transaction and therefore not obeying the ACID rules.
> >> >
> >> > If you want to take it out of the transaction then you could use
> >> > xp_cmdshell, but this would be relatively slow.
> >> >
> >> > You procedure is not coded to cater for multiple rows being updated and
> >> > is
> >> > therefore fundamentally flawed.
> >> >
> >> > John
> >> >
> >> > "Rob" wrote:
> >> >
> >> >> Actually, looks like the commit removes the Inserted memory
> >> >> variable....
> >> >> thus I am now asking...
> >> >> CREATE TRIGGER testtrigger ON dbo.Orders
> >> >> FOR INSERT, UPDATE, DELETE
> >> >> AS
> >> >>
> >> >> DECLARE @OrderID VARCHAR(20)
> >> >>   SELECT @OrderID = OrderNumber
> >> >>   FROM Inserted
> >> >>
> >> >> commit transaction
> >> >>
> >> >>
> >> >> BEGIN
> >> >>      EXEC prProcessOrder @OrderID
> >> >>   END
> >> >>
> >> >>
> >> >> "Rob" <rwch***@comcast.net> wrote in message
> >> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
> >> >> > If I want the row inserted into the table regardless of whether or
> >> >> > not
> >> >> > the
> >> >> > Stored proc completes successfully, then is there any problem with
> >> >> > employing the following technique (placing a "commit transaction"
> >> >> > on
> >> >> > the
> >> >> > first line) ?
> >> >> >
> >> >> >
> >> >> > CREATE TRIGGER testtrigger ON dbo.Orders
> >> >> > FOR INSERT, UPDATE, DELETE
> >> >> > AS
> >> >> > commit transaction
> >> >> >
> >> >> > DECLARE @OrderID VARCHAR(20)
> >> >> > SELECT @OrderID = OrderNumber
> >> >> > FROM Inserted
> >> >> >
> >> >> > BEGIN
> >> >> >     EXEC prProcessOrder @OrderID
> >> >> > END
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

12 Sep 2006 12:15 PM

Rob

Hi John,

> declare @ErrorCode int
> Select @ErrorCode = @@Error
> Seems a total waste of time as @ErrorCode will always be 0 after the
> declaration

Agreed, unless of course you made a syntax error in the declaration. (This
code was taken directly from a book written by Dejan Sunderic - Sql server
2000 stored procedures - page 305)

>You datatypes are not consistent between tables which leads to errors such
>as
>"Server: Msg 8152, Level 16, State 9, Procedure prProcessOrder, Line 9
>String or binary data would be truncated."

I guess I did not present this too clearly.... my datatype did not match on
purpose, as I wanted the stored procedure (called from the trigger) to fail
on purpose. My point was that I wanted the original insert to work
(provided that it would work if no trigger were on the table), even if the
stored procedure called from the trigger fails.

Thank you for you help on this matter. I do not want to use this method,
but I need some ammo to talk them out of it. I also need to come up with a
better solution.

Rob

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:FBA1544B-95B8-4300-94BE-7C518B6D1BCC@microsoft.com...
> Hi Rob
>
> Your error handling is very poor read
> http://www.sommarskog.se/error-handling-II.html on how/what you should be
> checking
>
> For instance:
>
> declare @ErrorCode int
> Select @ErrorCode = @@Error
>
> -- The following code causes failure due to incorrect datatype
> If @ErrorCode = 0
>
> Seems a total waste of time as @ErrorCode will always be 0 after the
> declaration.
>
> You datatypes are not consistent between tables which leads to errors such
> as
> "Server: Msg 8152, Level 16, State 9, Procedure prProcessOrder, Line 9
> String or binary data would be truncated."
>
> After sorting this out you will not get the entry in the log that you
> require if you did something like:
> Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912'
> SELECT * FROM ORDERS
> SELECT * FROM ORDERSLOG
> SELECT * FROM [dbo].[OrdersSuccessFail]
>
> Insert into Orders (OrderNumber,shipped) select 'ABCDEF', '20060912'
> /*
> Server: Msg 2627, Level 14, State 1, Line 1
> Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate
> key
> in object 'Orders'.
> The statement has been terminated.
> */
> SELECT * FROM ORDERS
> SELECT * FROM ORDERSLOG
> SELECT * FROM [dbo].[OrdersSuccessFail]
> /* Nothing has been logged */
>
> Erlands point about this failing on SQL 2005 may also indicate that the
> behavour of this on SQL 2000 may not be guaranteed in future
> patches/hotfixes
>
> John
>
>
>
>
> "Rob" wrote:
>
>> Hi John,
>>
>> When I tested this it did appear to "work"... I do know it is very ugly.
>> They are wanting to do this on a temporary basis...
>>
>> If you insert an OrderNumber < 6 characters and a valid date, the
>> transactions sticks, even if the sp fails... sample below...
>>
>> insert into Orders select '1111','1/1/2006'
>>
>> Thanks,
>> Rob
>>
>>
>> CREATE TABLE [dbo].[Orders] (
>> [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL ,
>> [Shipped] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[OrdersLog] (
>> [OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL ,
>> [Shipped] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[OrdersRaw] (
>> [Ord] [char] (6) COLLATE Latin1_General_BIN NULL ,
>> [DatShp] [varchar] (50) COLLATE Latin1_General_BIN NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[OrdersSuccessFail] (
>> [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL ,
>> [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL
>> ) ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[Orders] ADD
>> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
>> (
>>   [OrderNumber]
>> ) ON [PRIMARY]
>> GO
>>
>> SET QUOTED_IDENTIFIER ON
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>> CREATE TRIGGER testtrigger ON dbo.Orders
>> FOR INSERT
>> AS
>>
>> DECLARE @OrderID VARCHAR(20)
>>   SELECT @OrderID = OrderNumber
>>   FROM Inserted
>>
>> COMMIT   TRANSACTION
>>
>>
>> BEGIN TRANSACTION
>> insert into OrdersSuccessFail select @OrderID, 'Fail'
>> COMMIT   TRANSACTION
>>
>> BEGIN
>>      EXEC prProcessOrder @OrderID
>>   END
>>
>>
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>> CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS
>>
>> declare @ErrorCode int
>> Select @ErrorCode = @@Error
>>
>> -- The following code causes failure due to incorrect datatype
>> If @ErrorCode = 0
>> Begin
>>    Insert into OrdersLog select @OrderId, 'mmm'
>>    Select @ErrorCode = @@Error
>> End
>>
>> If @ErrorCode = 0
>> begin
>>
>>   update OrdersSuccessFail set SuccessFail = 'Success' where OrderId =
>> @OrderId
>>
>> end
>>
>> GO
>>
>>
>>
>>
>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> news:5ED03271-A943-4BF3-AC38-4A02FA7A2EEF@microsoft.com...
>> > Hi Rob
>> >
>> > As Erland has stated it will fail silently in SQL 2000 therefore it is
>> > a
>> > non-starter.
>> >
>> > You should never assume that you will always only insert/delete/update
>> > single rows a table; the requirements in the future may change and you
>> > may
>> > not be there, or may not be aware of the intention to make the change
>> > that
>> > results in multiple rows being updated, leading to
>> > inconsistent/corrupted
>> > data.
>> >
>> > John
>> >
>> > "Rob" wrote:
>> >
>> >> Hi John,
>> >>
>> >> I do not really like doing this.   I know it goes against the
>> >> grain....
>> >> but
>> >> I am told that they want to commit the insert regardless of whether
>> >> the
>> >> sp
>> >> runs ok...
>> >>
>> >> Some more info...
>> >>
>> >> The insert into the table is an isolated insert (not part of a larger
>> >> transaction). Also, only 1 row is added at a time (no bulk inserts).
>> >>
>> >> The question I have, and I want to be sure on this is....
>> >>
>> >> Does the "commit transaction" in the code below apply soley to
>> >> committing
>> >> the Insert into the "Orders" table.   Can it cause other issues ?
>> >>
>> >> CREATE TRIGGER testtrigger ON dbo.Orders
>> >> FOR INSERT AS
>> >>
>> >> DECLARE @OrderID VARCHAR(20)
>> >>    SELECT @OrderID = OrderNumber
>> >>    FROM Inserted
>> >>
>> >> commit transaction
>> >>
>> >>
>> >> BEGIN
>> >>       EXEC prProcessOrder @OrderID
>> >>    END
>> >>
>> >> Thanks,
>> >> Rob
>> >>
>> >>
>> >>
>> >>
>> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> >> news:455F7EE5-4FFE-46A2-ADD6-B29BD3B4785F@microsoft.com...
>> >> > Hi Rob
>> >> >
>> >> > Commiting the transaction in the trigger may be ok if you only have
>> >> > one
>> >> > stored procedure to update the table, but if the table gets updated
>> >> > in
>> >> > a
>> >> > multitude of situations there may be a situation where you are
>> >> > prematurely
>> >> > commiting a transaction and therefore not obeying the ACID rules.
>> >> >
>> >> > If you want to take it out of the transaction then you could use
>> >> > xp_cmdshell, but this would be relatively slow.
>> >> >
>> >> > You procedure is not coded to cater for multiple rows being updated
>> >> > and
>> >> > is
>> >> > therefore fundamentally flawed.
>> >> >
>> >> > John
>> >> >
>> >> > "Rob" wrote:
>> >> >
>> >> >> Actually, looks like the commit removes the Inserted memory
>> >> >> variable....
>> >> >> thus I am now asking...
>> >> >> CREATE TRIGGER testtrigger ON dbo.Orders
>> >> >> FOR INSERT, UPDATE, DELETE
>> >> >> AS
>> >> >>
>> >> >> DECLARE @OrderID VARCHAR(20)
>> >> >>   SELECT @OrderID = OrderNumber
>> >> >>   FROM Inserted
>> >> >>
>> >> >> commit transaction
>> >> >>
>> >> >>
>> >> >> BEGIN
>> >> >>      EXEC prProcessOrder @OrderID
>> >> >>   END
>> >> >>
>> >> >>
>> >> >> "Rob" <rwch***@comcast.net> wrote in message
>> >> >> news:FaCdnTYtv_T6x57YnZ2dnUVZ_vKdnZ2d@comcast.com...
>> >> >> > If I want the row inserted into the table regardless of whether
>> >> >> > or
>> >> >> > not
>> >> >> > the
>> >> >> > Stored proc completes successfully, then is there any problem
>> >> >> > with
>> >> >> > employing the following technique (placing a "commit
>> >> >> > transaction"
>> >> >> > on
>> >> >> > the
>> >> >> > first line) ?
>> >> >> >
>> >> >> >
>> >> >> > CREATE TRIGGER testtrigger ON dbo.Orders
>> >> >> > FOR INSERT, UPDATE, DELETE
>> >> >> > AS
>> >> >> > commit transaction
>> >> >> >
>> >> >> > DECLARE @OrderID VARCHAR(20)
>> >> >> > SELECT @OrderID = OrderNumber
>> >> >> > FROM Inserted
>> >> >> >
>> >> >> > BEGIN
>> >> >> >     EXEC prProcessOrder @OrderID
>> >> >> > END
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

10 Sep 2006 9:36 AM

Erland Sommarskog

[posted and mailed, please reply in news]

Rob (rwch***@comcast.net) writes:

Show quote

> Actually, looks like the commit removes the Inserted memory variable....
> thus I am now asking...
> CREATE TRIGGER testtrigger ON dbo.Orders
> FOR INSERT, UPDATE, DELETE
> AS
>
> DECLARE @OrderID VARCHAR(20)
>   SELECT @OrderID = OrderNumber
>   FROM Inserted
>
> commit transaction
>
>
> BEGIN
>      EXEC prProcessOrder @OrderID
>   END

No this is a very bad idea. On SQL 2000 this fails silently, but on
SQL 2005 you will get an error message. As you all you do is to run
an INSERT it may seem OK. But say that you run this batch:

   INSERT tbl(...)
      SELECT ....
   PRINT 'I have inserted!'

You will find that the PRINT statement is never executed. When SQL
Server finds that the transaction count during the execution of a
trigger, SQL Server aborts the batch, and if there was an outstanding
transaction, it is rolled back. (There can still be a transaction active
despite your COMMIT, as transactions can be nested.)

The trigger is also flawed since it reads the contents from inserted
into a variable. If many rows were affected, there will be many rows
in inserted.

A trigger is part of the transaction what is defined by the statement
that fires the trigger. This means that if the trigger fails, the
statement should fail, because statement + trigger should be atomic.

If you want the INSERT to happen, no matter if prProcessOrder succeeds
or not, you should invoke prProcessOrder in some other way. Maybe set
up an Agent job that scans for unprocessed orders periodically. On SQL
2005 I suspect that you would involve Service Broker one way or another.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

11 Sep 2006 4:24 PM

Rob

Hi Erland ,

Using sql server 2000...

I do not really like doing this.   I know it goes against the grain.... but
I am told that they want to commit the insert regardless of whether the sp
runs ok...

Some more info...

The insert into the table is an isolated insert (not part of a larger
transaction). Also, only 1 row is added at a time (no bulk inserts).

The question I have, and I want to be sure on this is....

Does the "commit transaction" in the code below apply soley to committing
the Insert into the "Orders" table.   Can it cause other issues ?

CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT AS

DECLARE @OrderID VARCHAR(20)
   SELECT @OrderID = OrderNumber
   FROM Inserted

commit transaction

BEGIN
      EXEC prProcessOrder @OrderID
   END

Thanks,
Rob

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns983A760478A8DYazorman@127.0.0.1...
> [posted and mailed, please reply in news]
>
> Rob (rwch***@comcast.net) writes:
>> Actually, looks like the commit removes the Inserted memory variable....
>> thus I am now asking...
>> CREATE TRIGGER testtrigger ON dbo.Orders
>> FOR INSERT, UPDATE, DELETE
>> AS
>>
>> DECLARE @OrderID VARCHAR(20)
>>   SELECT @OrderID = OrderNumber
>>   FROM Inserted
>>
>> commit transaction
>>
>>
>> BEGIN
>>      EXEC prProcessOrder @OrderID
>>   END
>
> No this is a very bad idea. On SQL 2000 this fails silently, but on
> SQL 2005 you will get an error message. As you all you do is to run
> an INSERT it may seem OK. But say that you run this batch:
>
>   INSERT tbl(...)
>      SELECT ....
>   PRINT 'I have inserted!'
>
> You will find that the PRINT statement is never executed. When SQL
> Server finds that the transaction count during the execution of a
> trigger, SQL Server aborts the batch, and if there was an outstanding
> transaction, it is rolled back. (There can still be a transaction active
> despite your COMMIT, as transactions can be nested.)
>
> The trigger is also flawed since it reads the contents from inserted
> into a variable. If many rows were affected, there will be many rows
> in inserted.
>
> A trigger is part of the transaction what is defined by the statement
> that fires the trigger. This means that if the trigger fails, the
> statement should fail, because statement + trigger should be atomic.
>
> If you want the INSERT to happen, no matter if prProcessOrder succeeds
> or not, you should invoke prProcessOrder in some other way. Maybe set
> up an Agent job that scans for unprocessed orders periodically. On SQL
> 2005 I suspect that you would involve Service Broker one way or another.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

11 Sep 2006 5:54 PM

Alexander Kuznetsov

Rob,

trigger is no place for such logic, because if your insert fails, your
trigger does not fire:

create table a(i int check(i>0))
go
create trigger a_ins on a for insert
as
print 'Trigger fired'
go
insert a values(1)
go

Trigger fired

(1 row(s) affected)

insert a values(-1)
go

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__a__i__4316F928'. The conflict occurred in database 'DominoV3',
table 'a', column 'i'.
The statement has been terminated.

drop table a

I would use savepoints.

11 Sep 2006 9:41 PM

Erland Sommarskog

Rob (rwch***@comcast.net) writes:

> Using sql server 2000...
>
> I do not really like doing this. I know it goes against the grain....
> but I am told that they want to commit the insert regardless of whether
> the sp runs ok...

The idea as such is not wrong. There may be also sorts of business
rules that mandates. You just have to make a good implementation.

> Some more info...
>
> The insert into the table is an isolated insert (not part of a larger
> transaction). Also, only 1 row is added at a time (no bulk inserts).

But it still fails, and it will not fail silenly, the day you move
to SQL 2005.

Is there any reason why you cannot just make put INSERT in a stored
procedure that first inserts the row, and then calls the other stored
procedure?

11 Sep 2006 10:15 PM

Rob

Thanks for responding Erland....

> Is there any reason why you cannot just make put INSERT in a stored
> procedure that first inserts the row, and then calls the other stored
> procedure?

The program that inserts the row into the table to begin with is owned by
another entity... I believe it is a VB6 app... but I am assured only 1 row
is inserted at a time (no bulk inserts) and it is not part of a larger
transaction.

> But it still fails, and it will not fail silenly, the day you move
> to SQL 2005.

I did include a test that I believe shows that it will not necessarily
fail...as long as the error is related to the sp that is called in the
trigger. (see below)

They have no plans to move to SQL 2005 any time soon. I also think SQL 2005
transact sql includes Try-Catch logic that would allow you to by pass any of
the sp errors.

Can placing the Commit Transaction (which I am assuming would only apply to
the implicit Begin Transaction related to the trigger) really bugger things
up in any way ?

Thanks,
Rob

*****************************************************
When I tested this it did appear to "work"... I do know it is very ugly.
They are wanting to do this on a temporary basis...

If you insert an OrderNumber < 6 characters and a valid date, the
transactions sticks, even if the sp fails... sample below...

insert into Orders select '1111','1/1/2006'

CREATE TABLE [dbo].[Orders] (
[OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL ,
[Shipped] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrdersSuccessFail] (
[OrderId] [char] (6) COLLATE Latin1_General_BIN NULL ,
[SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderNumber]
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER testtrigger ON dbo.Orders
FOR INSERT
AS

DECLARE @OrderID VARCHAR(20)
SELECT @OrderID = OrderNumber
FROM Inserted

COMMIT   TRANSACTION

BEGIN TRANSACTION
insert into OrdersSuccessFail select @OrderID, 'Fail'
COMMIT   TRANSACTION

BEGIN
     EXEC prProcessOrder @OrderID
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS

declare @ErrorCode int
Select @ErrorCode = @@Error

-- The following code causes failure due to incorrect datatype
If @ErrorCode = 0
Begin
   Insert into OrdersLog select @OrderId, 'mmm'
   Select @ErrorCode = @@Error
End

If @ErrorCode = 0
begin

update OrdersSuccessFail set SuccessFail = 'Success' where OrderId =
@OrderId

end

GO

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns983BF0F70AB2Yazorman@127.0.0.1...
> Rob (rwch***@comcast.net) writes:
>> Using sql server 2000...
>>
>> I do not really like doing this. I know it goes against the grain....
>> but I am told that they want to commit the insert regardless of whether
>> the sp runs ok...
>
> The idea as such is not wrong. There may be also sorts of business
> rules that mandates. You just have to make a good implementation.
>
>> Some more info...
>>
>> The insert into the table is an isolated insert (not part of a larger
>> transaction). Also, only 1 row is added at a time (no bulk inserts).
>
> But it still fails, and it will not fail silenly, the day you move
> to SQL 2005.
>
> Is there any reason why you cannot just make put INSERT in a stored
> procedure that first inserts the row, and then calls the other stored
> procedure?
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

12 Sep 2006 7:27 AM

Erland Sommarskog

Rob (rwch***@comcast.net) writes:

Show quote

> The program that inserts the row into the table to begin with is owned by
> another entity... I believe it is a VB6 app... but I am assured only 1 row
> is inserted at a time (no bulk inserts) and it is not part of a larger
> transaction.
>
>> But it still fails, and it will not fail silenly, the day you move
>> to SQL 2005.
>
> I did include a test that I believe shows that it will not necessarily
> fail...as long as the error is related to the sp that is called in the
> trigger. (see below)
>
> They have no plans to move to SQL 2005 any time soon. I also think SQL
> 2005 transact sql includes Try-Catch logic that would allow you to by
> pass any of the sp errors.

Yes, but then someone would have to rewrite the trigger. The first thing
that till happen is that it will explode in the face of someone.

> Can placing the Commit Transaction (which I am assuming would only apply
> to the implicit Begin Transaction related to the trigger) really bugger
> things up in any way ?

This is about robustness. It may work as long as the application looks like
it does today. But in six months someone else writes another operation
against this table, and where the INSERT is part of a longer transaction.
His code will not work, and it could take him several days to understand
why.

If the procedure is to be executed indepently, it should not be part of
the trigger. Set up a job that checks the table for new inserts. And if
that is not easy to do that, use the trigger to insert rows into a queue
table that the processing SP can take care of.

But don't implement a solution that can be very expensive for your client
once you are out of the door.

14 Sep 2006 10:24 PM

Ryan

Rob,

Does it have to happen immediately?

Can you not have the trigger do something like...
Insert Into OrderQueue (OrderID)
Select OrderID from Inserted

Then have a job run every minute that loops through the OrderQueue table and
updates a processed flag or Processed Date field, and calls the SP once for
each order (Or even better, re-write the SP to just batch process everything
in the OrderQueue that has a Processed of 0/null).

If you want it even faster than a minute, just have the job loop back on
itself. I.E. Step 1 call SP then GotoStep 2. Step 2 call then goto Step
1. On failure, goto Step 3 - email or page that it failed, then goto step 1.

This is all a hack, I know, but I have used it successfully to automate near
real-time workflows, and it will still happen if something decides to input
more than 1 row into the orders table at a time.

Ryan

Show quote

"Rob" wrote:

> Thanks for responding Erland....
>
> > Is there any reason why you cannot just make put INSERT in a stored
> > procedure that first inserts the row, and then calls the other stored
> > procedure?
>
> The program that inserts the row into the table to begin with is owned by
> another entity... I believe it is a VB6 app... but I am assured only 1 row
> is inserted at a time (no bulk inserts) and it is not part of a larger
> transaction.
>
> > But it still fails, and it will not fail silenly, the day you move
> > to SQL 2005.
>
> I did include a test that I believe shows that it will not necessarily
> fail...as long as the error is related to the sp that is called in the
> trigger. (see below)
>
> They have no plans to move to SQL 2005 any time soon. I also think SQL 2005
> transact sql includes Try-Catch logic that would allow you to by pass any of
> the sp errors.
>
> Can placing the Commit Transaction (which I am assuming would only apply to
> the implicit Begin Transaction related to the trigger) really bugger things
> up in any way ?
>
> Thanks,
> Rob
>
> *****************************************************
> When I tested this it did appear to "work"... I do know it is very ugly.
> They are wanting to do this on a temporary basis...
>
> If you insert an OrderNumber < 6 characters and a valid date, the
> transactions sticks, even if the sp fails... sample below...
>
> insert into Orders select '1111','1/1/2006'
>
>
> CREATE TABLE [dbo].[Orders] (
> [OrderNumber] [char] (6) COLLATE Latin1_General_BIN NOT NULL ,
> [Shipped] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[OrdersSuccessFail] (
> [OrderId] [char] (6) COLLATE Latin1_General_BIN NULL ,
> [SuccessFail] [varchar] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Orders] ADD
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
>   [OrderNumber]
> ) ON [PRIMARY]
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE TRIGGER testtrigger ON dbo.Orders
> FOR INSERT
> AS
>
> DECLARE @OrderID VARCHAR(20)
>   SELECT @OrderID = OrderNumber
>   FROM Inserted
>
> COMMIT   TRANSACTION
>
>
> BEGIN TRANSACTION
> insert into OrdersSuccessFail select @OrderID, 'Fail'
> COMMIT   TRANSACTION
>
> BEGIN
>      EXEC prProcessOrder @OrderID
>   END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE prProcessOrder @OrderId as char(6) AS
>
> declare @ErrorCode int
> Select @ErrorCode = @@Error
>
> -- The following code causes failure due to incorrect datatype
> If @ErrorCode = 0
> Begin
>    Insert into OrdersLog select @OrderId, 'mmm'
>    Select @ErrorCode = @@Error
> End
>
> If @ErrorCode = 0
> begin
>
>   update OrdersSuccessFail set SuccessFail = 'Success' where OrderId =
> @OrderId
>
> end
>
> GO
>
>
>
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message
> news:Xns983BF0F70AB2Yazorman@127.0.0.1...
> > Rob (rwch***@comcast.net) writes:
> >> Using sql server 2000...
> >>
> >> I do not really like doing this.   I know it goes against the grain....
> >> but I am told that they want to commit the insert regardless of whether
> >> the sp runs ok...
> >
> > The idea as such is not wrong. There may be also sorts of business
> > rules that mandates. You just have to make a good implementation.
> >
> >> Some more info...
> >>
> >> The insert into the table is an isolated insert (not part of a larger
> >> transaction). Also, only 1 row is added at a time (no bulk inserts).
> >
> > But it still fails, and it will not fail silenly, the day you move
> > to SQL 2005.
> >
> > Is there any reason why you cannot just make put INSERT in a stored
> > procedure that first inserts the row, and then calls the other stored
> > procedure?
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
> >
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>
>

11 Sep 2006 10:23 PM

Rob

Erland,

If you decide to try my test... I forgot a table required...

CREATE TABLE [dbo].[OrdersLog] (
[OrderNumber] [char] (5) COLLATE Latin1_General_BIN NULL ,
[Shipped] [datetime] NULL
) ON [PRIMARY]
GO

Thanks,
Rob

Show quote