try catch in trigger - 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

General SQL Server Forums

New to SQL Server Programming

try catch in trigger

Author

Topic

marino
Starting Member

7 Posts

Posted - 2010-02-18 : 05:48:16

Hello, sorry for the english but i'm italian.

I'd like to sync in real time two table in sql server 2005 express.
To do this i've realized 3 trigger (one for insert, for update and for delete).

For example i'd like to sync table1 in DBmaster and table1 in DBslave.
DBmaster and DBslave are in the same sql istance but the mdf file are in two different disk(c: and d: for example, d: can be a removable device)

This is the trigger that I have used to sync table in case of update it and works correctly.

CREATE TRIGGER [Dbmaster].[dbo].[AggiornaTab]
ON [Dbmaster].[dbo].[table1]
after update
AS
begin try
delete from [DBslave].[dbo].[table1] where [DBslave_DB1].[dbo].[table1].ID in (select ID from deleted)
INSERT [DBslave_DB1].[dbo].[table1] SELECT * FROM inserted
end try
Begin Catch

End catch

If the Dbslave is not findable the update in DBmaster is blocked.
I'd like that the update in DBmaster not be blocked in every case.

Thanks for the attention

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 05:53:49

for real time sync isnt replication enough?

http://msdn.microsoft.com/en-us/library/ms165700(SQL.90).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

marino
Starting Member

7 Posts

Posted - 2010-02-18 : 06:00:28

quote:
Originally posted by visakh16

for real time sync isnt replication enough?

http://msdn.microsoft.com/en-us/library/ms165700(SQL.90).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

My sqlserver is express edition and replication, mirror and log shipping aren't supported.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 06:10:00

then put a rollback inside catch with proper error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

marino
Starting Member

7 Posts

Posted - 2010-02-18 : 06:22:34

quote:
Originally posted by visakh16

then put a rollback inside catch with proper error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If the DBslave isn't findble (for exmple if the removable drive is not plugged) the try block don't intercet error and every istruction in catch block isn't performed and also the update in DBmaster fail

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 06:46:33

From SQL Documentation:

"A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.

Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.

Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates."

If database is offline, or table does not exist, I think you get an error with >20 severity

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 07:43:33

error severity is 16...

-- Try...Catch is not working:
BEGIN TRY
select * from isntThere.dbo.isntThere
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH

-- Try...Catch IS working
BEGIN TRY
exec ('select * from isntThere.dbo.isntThere')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Read here:
http://msdn.microsoft.com/en-us/library/ms175976(SQL.90).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 08:04:57

Useful, thanks WebFred.

It was the next para in BoL:

"The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution."

So the SProc which caused the Trigger to fire COULD catch such an error?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 08:19:12

Looks like it is so:


create table isThere (test varchar(255))

create trigger test on isThere for insert as
insert isntThere select * from inserted

begin try
begin tran
insert isThere select 'test'
commit tran
end try
begin catch
SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
        rollback tran
end catch

select * from isThere

create table isntThere (test varchar(255))

begin try
begin tran
insert isThere select 'test'
commit tran
end try
begin catch
SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
        rollback tran
end catch

select * from isThere
select * from isntThere

drop table isThere
drop table isntThere

No, you're never too old to Yak'n'Roll if you're too young to die.

marino
Starting Member

7 Posts

Posted - 2010-02-18 : 12:28:37

Thanks for your replay.

In my tigger, if i use exec istruction, the try intercets the error but the update in DBmaster is interrupted.

CREATE TRIGGER [Dbmaster].[dbo].[AggiornaTab]
ON [Dbmaster].[dbo].[table1]
after update
AS
begin try
exec ('delete from [DBslave].[dbo].[table1] where [DBslave_DB1].[dbo].[table1].ID in (select ID from deleted')
exec('INSERT [DBslave_DB1].[dbo].[table1] SELECT * FROM inserted')
end try
Begin Catch
print 'ISN'T OK'
End catch

So if the database DBslave is not findeble the query print 'ISN'T OK' but the update in DBmaster was aborted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 12:35:31

print 'ISN'T OK' should be print 'ISN''T OK' else it will throw error

see
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

marino
Starting Member

7 Posts

Posted - 2010-02-18 : 12:45:38

Sorry.
if i use print 'is not ok' i the same case.
The trigger cause the abort of update in DBmaster (and obviously in BDslave)

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 12:52:17

That is how I read the Documentation as to what will happen.

I don't know how you can prevent it.

You could check that the Table exists (in sys.systables) and only proceed if the table exists, but that will not help if the database/server is offline

marino
Starting Member

7 Posts

Posted - 2010-02-19 : 06:30:19

Exist another way to sync two database in real time with sql 2005 express?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 06:36:49

quote:
Originally posted by marino

Exist another way to sync two database in real time with sql 2005 express?

is both dbs express ones?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

marino
Starting Member

7 Posts

Posted - 2010-02-19 : 07:01:11

quote:
Originally posted by visakh16

quote:
Originally posted by marino

Exist another way to sync two database in real time with sql 2005 express?

is both dbs express ones?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

All Database server are express edition

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources