SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 2008 trigger problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

291 Posts

Posted - 01/26/2013 :  23:37:10  Show Profile  Reply with Quote
I am trying to write my first trigger on a sql server 2008 r2 database.
I keep getting a procedure error the 'INSERT INTO [dbo].[RPT_Trans_Audit]
(Package_ID, Received_Date, Download_Date)' statement listed below. I do not know what is wrong. Can you tell me what I need to change?

USE DEV2
GO
CREATE TRIGGER [dbo].[RPT_Trans_Audit] ON [RPT_Trans]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY

INSERT INTO [dbo].[RPT_Trans_Audit] (Package_ID, Received_Date, Download_Date)
SELECT Package_ID, Received_Date, Download_Date
FROM INSERTED
END TRY
GO

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/27/2013 :  07:02:10  Show Profile  Visit webfred's Homepage  Reply with Quote
the catch block is missing


Too old to Rock'n'Roll too young to die.
Go to Top of Page

jassie
Constraint Violating Yak Guru

291 Posts

Posted - 01/27/2013 :  11:11:25  Show Profile  Reply with Quote
Here is the exact code with the error message. Can you tell me how to put in a catch block?

Msg 102, Level 15, State 1, Procedure eRPT_Transaction_Audit, Line 15
Incorrect syntax near 'TRY'.

DEV
GO
CREATE TRIGGER [dbo].[eRPT_Transaction_Audit] ON [dbo].[eRPT_Transaction_Tracking]
AFTER INSERT
AS

BEGIN
SET NOCOUNT ON;

BEGIN TRY
INSERT INTO [dbo].[eRPT_Transaction_Audit] (Package_ID, Received_Date, Download_Date,
Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
FDR_Report_Date, Close_Date, File_Location,Response_Code)
SELECT Package_ID, Received_Date, Download_Date,
Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
FDR_Report_Date, Close_Date, File_Location,Response_Code FROM INSERTED
END TRY

END
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/27/2013 :  14:15:15  Show Profile  Reply with Quote
you're missing CATCH block. There should be a CATCH block immediately following a TRY block


DEV
GO
CREATE TRIGGER [dbo].[eRPT_Transaction_Audit] ON [dbo].[eRPT_Transaction_Tracking]
AFTER INSERT
AS

BEGIN
SET NOCOUNT ON;

BEGIN TRY
INSERT INTO [dbo].[eRPT_Transaction_Audit] (Package_ID, Received_Date, Download_Date,
Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
FDR_Report_Date, Close_Date, File_Location,Response_Code)
SELECT Package_ID, Received_Date, Download_Date,
Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
FDR_Report_Date, Close_Date, File_Location,Response_Code FROM INSERTED
END TRY 
 BEGIN CATCH
...
... some code here to catch the errors generated in TRY block
END CATCH

END
GO


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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 01/27/2013 :  17:24:43  Show Profile  Reply with Quote
Didn't see Visakh's post when I wrote this and posted (Had the window open for a while and just happened upon it).

Jassie, when you use a BEGIN TRY/END TRY block, it must ALWAYS be followed by BEGIN CATCH/END CATCH block. So you should add the catch block at the end. So your code should be something like this:
CREATE TRIGGER [dbo].[eRPT_Transaction_Audit] ON [dbo].[eRPT_Transaction_Tracking]
AFTER INSERT
AS

BEGIN
SET NOCOUNT ON;

BEGIN TRY
INSERT INTO [dbo].[eRPT_Transaction_Audit] (Package_ID, Received_Date, Download_Date,
Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
FDR_Report_Date, Close_Date, File_Location,Response_Code)
SELECT Package_ID, Received_Date, Download_Date,
Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
FDR_Report_Date, Close_Date, File_Location,Response_Code FROM INSERTED
END TRY 
BEGIN CATCH
	-- do some stuff here that you need to
	/* 
		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

END
GO
If the reason you are doing this is to ensure atomicity of the operations, you should add a begin tran and end tran also, and handle the transaction appropriately. There are two very good examples on this MSDN page, examples B and C: http://msdn.microsoft.com/en-us/library/ms175976.aspx

You should also be aware that if you rollback a transaction in the trigger, that will cause rollback of the insert/update/delete that triggered the transaction.


Edited by - James K on 01/27/2013 17:26:12
Go to Top of Page

jassie
Constraint Violating Yak Guru

291 Posts

Posted - 01/27/2013 :  20:15:53  Show Profile  Reply with Quote
The sql you gave me works correctly. I just submitted the query in a general query window. My problem is I do not know where the database trigger is located at.

I looked for the trigger under the database triggers and I can not find it. Thus do I need to do one of the following:

1. Make certain I have access to the trigger- correct permissions?

2. should I run the query as ' create a database trigger' in sql server management sutdio?

3. Do you have anyt other suggestions?

Edited by - jassie on 01/27/2013 21:24:39
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/27/2013 :  23:14:02  Show Profile  Reply with Quote
Under Table [dbo].[eRPT_Transaction_Tracking], you can find out the trigger which u have created

1)Expand the table [dbo].[eRPT_Transaction_Tracking],
2)Expand Triggers option..

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/27/2013 :  23:55:45  Show Profile  Reply with Quote
quote:
Originally posted by jassie

The sql you gave me works correctly. I just submitted the query in a general query window. My problem is I do not know where the database trigger is located at.

I looked for the trigger under the database triggers and I can not find it. Thus do I need to do one of the following:

1. Make certain I have access to the trigger- correct permissions?

2. should I run the query as ' create a database trigger' in sql server management sutdio?

3. Do you have anyt other suggestions?




you can get trigger code from sys.sql_modules view programatically

select * from sys.sql_modules WHERE OBJECT_NAME(object_id) = your trigger name


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000