Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
t-sql 2008 error message

Answered t-sql 2008 error message

  • Sunday, January 27, 2013 4:01 PM
     
     
    Sign In to Vote
    0

    This is the first time I am writing a trigger. I am getting the following error message on a sql server 2008 r2 database.

    "Msg 102, Level 15, State 1, Procedure eRPT_Transaction_Audit, Line 15 Incorrect syntax near 'TRY'. I do not know what is wrong with the following sql. Can you tell me what is wrong with the following sql:


    USE 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_name, Received_Date, Download_Date,
       Acknowledge_Date, Response_Docs_Date, Error_Report_Date,
       FDR_Report_Date, Close_Date, File_Location,Response_Code)
       SELECT Package_name, 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

    also note in the trigger, I am not listing the identity key for the table


     

All Replies

  • Sunday, January 27, 2013 4:05 PM
     
     Proposed Answer
    Sign In to Vote
    0

    Hi elizabeth,

            you need to add BEGIN CATCH ... END CATCH block after TRY block

    "A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error."

    Refer this link - http://msdn.microsoft.com/en-us/library/ms175976.aspx

    Thanks & Regards, sathya


     
  • Sunday, January 27, 2013 4:09 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi,

    You miss CATCH Block . Every TRY block must end with CATCH Block.

    Try this.

    BEGIN TRY
    --Your Sql Query
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;


    Check the Rules how to use TRY CATCH Block

    Using TRY...CATCH in Transact-SQL

    PS.Shakeer Hussain

     
  • Monday, January 28, 2013 2:43 AM
     
     
    Sign In to Vote
    0
    0

    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?

     
  • Monday, January 28, 2013 2:57 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Elizabeth,

                 Once you have executed the trigger statement and if it is successfully executed,it will be located under the table on which you have created trigger.

    navigate as shown in below image 

    SELECT Object_name(parent_id) TableName,name TriggerName FROM sys.[triggers] WHERE name like '%dEmployee%'

    you have created the trigger on table [dbo].[eRPT_Transaction_Tracking],so trigger will be created under that table,no need to search under database triggers.

    3 types of triggers :

    table | view

    Is the table or view on which the DML trigger is executed and is sometimes referred to as the trigger table or trigger view. Specifying the fully qualified name of the table or view is optional. A view can be referenced only by an INSTEAD OF trigger. DML triggers cannot be defined on local or global temporary tables.

    DATABASE

    Applies the scope of a DDL trigger to the current database. If specified, the trigger fires whenever event_type or event_group occurs in the current database.

    ALL SERVER

    Applies the scope of a DDL or logon trigger to the current server. If specified, the trigger fires whenever event_type or event_group occurs anywhere in the current server.

    Refer this link for better understanding - http://msdn.microsoft.com/en-us/library/ms189799.aspx