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 2008 Forums
 Transact-SQL (2008)
 2008 trigger problem

Author  Topic 

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-01-26 : 23:37:10
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-27 : 07:02:10
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

332 Posts

Posted - 2013-01-27 : 11:11:25
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

52326 Posts

Posted - 2013-01-27 : 14:15:15
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 17:24:43
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.

Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-01-27 : 20:15:53
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?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-27 : 23:14:02
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

52326 Posts

Posted - 2013-01-27 : 23:55:45
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
   

- Advertisement -