| Author |
 Topic  |
|
|
jassie
Posting Yak Master
114 Posts |
 Posted - 01/26/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 01/27/2013 : 07:02:10
|
the catch block is missing
Too old to Rock'n'Roll too young to die. |
 |
|
|
jassie
Posting Yak Master
114 Posts |
Posted - 01/27/2013 : 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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 01/27/2013 : 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/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/27/2013 : 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.
|
Edited by - James K on 01/27/2013 17:26:12 |
|
|
|
jassie
Posting Yak Master
114 Posts |
Posted - 01/27/2013 : 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?
|
Edited by - jassie on 01/27/2013 21:24:39 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 01/27/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 01/27/2013 : 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/
|
|
|
| |
Topic |
|
|
|
2008 trigger problem
