September 16, 2009 at 11:14 am
Hi to all SQL Master
I am using a trigger to connect and synchronized two database.
I am thinking if SQL has the capability to store error from a trigger? if yes, where can i find it?
All i found in SQl is the SQL Log but specifically we need a error log for trigger only? Do you think SQl has the capability of that?
Thanks
September 16, 2009 at 1:17 pm
Hi Guys
Browsing the net i found this useful information with regards to the issue i posted
CREATE TABLE error_log (
timestamp DATETIME,
username NVARCHAR(30),
instance INT,
database_name NVARCHAR(50),
error_stack NVARCHAR(2000)
);
CREATE OR REPLACE TRIGGER LogErrors
AFTER SERVERERROR ON DATABASE
BEGIN
INSERT INTO error_log
VALUES (SYS.DATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
END LogErrors;
One issue: I think this script is not design in SQL2005 can you please help me to make this script applicable in SQL2005 to resolve the issue below.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OR'.
Msg 128, Level 15, State 1, Line 5
The name "SYSDATE" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Thanks
September 17, 2009 at 7:40 am
This looks like the code you found is for Oracle.
There is not a way that I know of to do what this trigger does because this looks like it creates a trigger on the database that fires whenever there is an server error in the database.
If you shared the code in your trigger someone might be able to help you do what you need. What will be most difficult is that the trigger takes place within a transaction so errors cause rollbacks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 9:01 am
Hi there,
Yeah it's true this script is created for Oracle so I am looking a way in where i was able to do it also in SQL 2005 do you think there is a way to do it. I was terribly need in help on how can I trap all the SQl error and log it in a table for references.
Do you have any hint?
Please do help me.
September 17, 2009 at 9:26 am
Jack Corbett (9/17/2009)
There is not a way that I know of to do what this trigger does because this looks like it creates a trigger on the database that fires whenever there is an server error in the database.
I think this answers your latest question.
If you shared the code in your trigger someone might be able to help you do what you need. What will be most difficult is that the trigger takes place within a transaction so errors cause rollbacks.
And if you you share some code or an example of what you are doing someone may be able to share a way to do what you want.
You may want to look up TRY...CATCH in BOL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 9:52 am
Yes, I do receive your reply. So based on your reply i need to add a Try catch in every trigger that I have or is there another option around?.. If tracking Trigger Error is not possible how about SQL Error how can i stored it in a table?
September 17, 2009 at 9:57 am
Jack Corbett (9/17/2009)
If you shared the code in your trigger someone might be able to help you do what you need. What will be most difficult is that the trigger takes place within a transaction so errors cause rollbacks.
And if you you share some code or an example of what you are doing someone may be able to share a way to do what you want.
.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 10:22 am
As of now, All i have is a trigger example :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [dbo].[trg_SynctblICInventoryCategory]
ON [dbo].[tblICInventoryCategory]
--WITH ENCRYPTION
FOR INSERT, UPDATE
AS
BEGIN
INSERT INTO [SampleData].dbo.ac_CatalogNodes (
CategoryID
, CatalogNodeID
, CatalogNodeTypeID
, OrderBy)
SELECT
isnull(c.CategoryID, 0)
,isnull(p.ProductID, 0)
, 1
, 0
FROM
tblicinventory I
INNER JOIN [SampleData].dbo.ac_Products p
on p.intVCCntID = I.cntID
INNER JOIN tblicinventorycategory ic
on ic.strProductID = I.strProductID
INNER JOIN tbliccategories icc
on icc.intCategoryID = ic.intCategoryID
INNER JOIN [SampleData].dbo.ac_Categories c
on c.intVCCategoryID = icc.cntID
LEFT OUTER JOIN [SampleData].dbo.ac_CatalogNodes AS cn
ON (p.ProductID = cn.CatalogNodeID AND c.CategoryID = cn.CategoryID AND cn.CatalogNodeTypeID = 1)
WHERE cn.CategoryID IS null AND (I.strMatrixBase = '' OR I.strMatrixBase IS null)
END
then this is the sample scenario: what if along the way upon running the trigger it cause an error how can i log those error in a temptable?
September 17, 2009 at 11:13 am
What you do is add error handling to the procs that are doing the inserts, updates, and deletes in your database.
If you have a Try Catch structure in an insert proc, for example, you can have it roll back the insert and then log the error into a log table.
You don't want to do that in a trigger, because if the proc creating the error rolls back, it will roll back the trigger, which means it will undo the logging. It needs to be at the highest level of code, which means the procs, not in triggers.
That's the correct way to handle this kind of thing.
Microsoft has examples in Books Online and MSDN in the chapters on Try Catch. You should be able to adapt those examples to your specific needs. If you have trouble with that, post the problematic code here, and we'll see if we can help you debug it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply