Trigger problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger problem

Author

Topic

tribune
Posting Yak Master

105 Posts

Posted - 2005-10-03 : 04:29:33

Here's my scenario!

I have one database for orders. Another for emails.

When updates are made to an order (orders table in orders database), I have a trigger on the orders table that copies a few pieces of information to the email database. However, if the email database is offline momentarily, the update fails completely (without an error message).

Say for example, I try to update the status of an order from 'N' for new, to 'C' for cancelled, in enterprise manager. When the email database is offline EM just ignores the transaction and sets the status back to 'N' without any error message. I've tried adding error handling and raising errors from my trigger with no luck.

Basically what I need is for SQL to accept the transaction and commit it, then I can sync the data up later when the email database comes back online with a special sql script.

How do I do this? I've also tried putting the trigger code into a stored procedure and executing the SPROC from the trigger but get the same result.

Thanks

Kristen
Test

22859 Posts

Posted - 2005-10-03 : 06:02:32

If the EMail database/system not "robust" I would have the trigger put the EMail in a table within the database. Then I would run a scheduled routine (every minute perhaps?) to look for rows in this new table, loop round them, pass them to the Email system and if successful delete them from the new table.

(Or you could keep them but flag with data&time when that record was "Mailed" if the Audit History is useful - but then you need some other housekeeping routine to clear out the old / stale ones!)

Kristen

tribune
Posting Yak Master

105 Posts

Posted - 2005-10-03 : 15:52:43

Kristen, thank you for your advice, I will try this.

Would you happen to know why SQL Server is exhibiting this type of behavior with my trigger? That is, can sql server throw errors in triggers at all?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-03 : 22:19:42

sql server can register errors, how are you handling the error?
can you post your trigger so we can take a look?

quote:
Originally posted by tribune

Kristen, thank you for your advice, I will try this.

Would you happen to know why SQL Server is exhibiting this type of behavior with my trigger? That is, can sql server throw errors in triggers at all?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-03 : 23:47:59

"That is, can sql server throw errors in triggers at all"

Yup ... Deadlock would be one. And they could cause the transaction to timeout (from the applications perspective).

And certainly if any database/table/column that it references can't be found then its going to raise an error.

But you'd know about it. However, to "know about it" the application would have to detect the error - that means examining the ADO errors collection (or somesuch) - and given that, for example, INSERT does not return a recordset its quite common for applications not to bother trying to process a recordset to discover that there is actually an errors collection!

"if the email database is offline momentarily"

That phrase bothered me - I assumed you mean that the Email database was on a different server or somesuch, but your subsequent replies indicates its on the same server - what causes it to do "offline momentarily" ?

Kristen

tribune
Posting Yak Master

105 Posts

Posted - 2005-10-04 : 03:34:21

Ok, let me be a little more descriptive.

I've figured out that raising errors in my trigger does work (EM doesn't tell me about it, but QA does).

Basically my question now is how can I get a transaction to complete even if a trigger encounters an error.

Here's my trigger:


/*
Description:
Syncs new booking orders to the Delivery database.
*/
CREATE trigger OnlineBookingOrders_trgupd_SendNewOrderToDelivery
on OnlineBookingOrders
for update
as

begin

if exists
	(
	select 'true'
	from inserted i join deleted d on i.BookingOrderID = d.BookingOrderID
	where i.OrderStatus = 'N' and d.OrderStatus <> 'N'
	)
begin
	insert into Delivery.dbo.OnlineBookingOrders
	(
		BookingOrderID,
		OrderStatus,
		UnitID,
		Checkin,
		Checkout,
		FirstName,
		LastName,
		Email
	)
	select
		BookingOrderID,
		'N',
		UnitID,
		Checkin,
		Checkout,
		FirstName,
		LastName,
		Email
	from
		inserted

	if @@error <> 0
	begin
		declare @BookingOrderID int
		select @BookingOrderID = BookingOrderID from inserted
		raiserror(60003, 16,  1, @BookingOrderID)
	end
end

end

Now, lets say I issue a SQL statement such as:


sp_addmessage 60003, 16, 'Failed to export order # %s to Delivery database.'

update OnlineBookingOrders set OrderStatus = 'N' where BookingOrderID = 317

if @@error <> 0
begin
	print 'Error has been raised!'
end

When the Delivery database is online, the statement works. But if it were offline, on another server which is frozen, et cetera, whatever the scenario may be, then the whole statement fails and the order status is NOT updated. However, it needs to at least be updated, then I can run a syncing script later when the Delivery database comes back online.

By the way, the results for the update statement are:


(1 row(s) affected)

Server: Msg 942, Level 14, State 4, Line 16
Database 'Delivery' cannot be opened because it is offline.

Which shows that @@error = 0 too. How can I handle this properly? Thanks!

tribune
Posting Yak Master

105 Posts

Posted - 2005-10-04 : 03:36:08

I could forgo the triggers and write a scheduled tasks which polls the database every 1-2 minutes, but for some instances, such as when an order is set to 'N' for new, I need the delivery database to be notified instantly so an email alert can be shipped out within a couple seconds. Also, that seems like it could add a lot of extra work for the server.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 03:38:55

you need a begin/commit tran to override the rollback

begin tran
[the query you need to commit no matter what]
commit tran
.
.
.

but do you really want to do this?

@@error will tell you the error encountered for last statement before you called @@error, so if that statement is successful you'll get 0

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-04 : 05:43:26

Your code looks a little "loose"

This is an UPDATE trigger, could "N"=New get into the record on the initial INSERT to OnlineBookingOrders? (If so you need this to handle INSERT too)

You check for the existence of a row where OrderStatus has changed from Not-"N" to "N":

1) Could that column also be NULL instead of "Not-N"?
2) You then do not repeat that constraint on what you insert into Delivery.dbo.OnlineBookingOrders - so one changed row matching the "Was not-N is now N" will cause ALL rows in "inserted" to be insert into Delivery.dbo.OnlineBookingOrders (AND all to be marked as "N" as you have hardwired that value)

You have only used the "dbo" owner when referencing table once. This will become a very subtle bug if the table subsequently (accidentally or deliberately) gets created for a non-dbo owner - best to always prefix all tables with "dbo." - better for performance too.

select @BookingOrderID = BookingOrderID from inserted
will select a random value from "inserted" when there is more that one row effected by the operation which called this trigger.

"Database 'Delivery' cannot be opened because it is offline"

How is that database going offline? Seems to me to be really scary for a production system! has it been restored from an MSDE system? (and thus set to the MSDE default of AUTO_CLOSE)

"that seems like it could add a lot of extra work for the server"

The database will probably run better if it has a batch of records to work on, rather than being given each one one-by-one - but it depends on the business requirement.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources