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.
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 DEV2GOCREATE TRIGGER [dbo].[RPT_Trans_Audit] ON [RPT_Trans] AFTER INSERT, UPDATE, DELETEASBEGINSET NOCOUNT ON;BEGIN TRY INSERT INTO [dbo].[RPT_Trans_Audit] (Package_ID, Received_Date, Download_Date) SELECT Package_ID, Received_Date, Download_Date FROM INSERTEDEND 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. |
|
|
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 15Incorrect syntax near 'TRY'.DEVGOCREATE TRIGGER [dbo].[eRPT_Transaction_Audit] ON [dbo].[eRPT_Transaction_Tracking] AFTER INSERTASBEGINSET 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 INSERTEDEND TRY ENDGO |
|
|
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 blockDEVGOCREATE TRIGGER [dbo].[eRPT_Transaction_Audit] ON [dbo].[eRPT_Transaction_Tracking]AFTER INSERTASBEGINSET NOCOUNT ON;BEGIN TRYINSERT 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 INSERTEDEND TRY BEGIN CATCH...... some code here to catch the errors generated in TRY blockEND CATCHENDGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 INSERTASBEGINSET NOCOUNT ON;BEGIN TRYINSERT 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 INSERTEDEND 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 CATCHENDGO 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.aspxYou 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. |
|
|
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? |
|
|
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 created1)Expand the table [dbo].[eRPT_Transaction_Tracking], 2)Expand Triggers option..--Chandu |
|
|
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 programaticallyselect * from sys.sql_modules WHERE OBJECT_NAME(object_id) = your trigger name ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|