Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sql server 2008 trigger question

Answered sql server 2008 trigger question

  • Sunday, January 27, 2013 4:40 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Sunday, January 27, 2013 4:43 AM
    Answerer
     
     Proposed Answer
    Sign In to Vote
    0

    PackageID column is probably defined as an IDENTITY property, so try the below

    INSERT INTO [dbo].[RPT_Trans_Audit] ( Received_Date, Download_Date)
                        SELECT  Received_Date, Download_Date
                       FROM INSERTED

    Also I see you do have BEGIN  but not END clause

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

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

    GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed As Answer by pituach Sunday, January 27, 2013 4:48 AM
    •  
     
  • Sunday, January 27, 2013 4:54 AM
     
     
    Sign In to Vote
    0
    The package_id is not the identity field. I left off the identity column to keep my question as short as possible. Thus do you think my problem is related to not having an 'end' statement?
     
  • Sunday, January 27, 2013 4:58 AM
     
     
    Sign In to Vote
    0

    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?

    Please post the exact error message you are getting so that we can better help you.

    You are missing the BEGIN CATCH/END CATCH block.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, January 27, 2013 4:59 AM added catch block note
    •  
     
  • Sunday, January 27, 2013 6:20 AM
    Answerer
     
     
    Sign In to Vote
    0
    As Dan already said, please post  the exact ERROR message.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Sunday, January 27, 2013 7:58 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Hi Wendy,

    1) As Uri pointed out you need an 'END'
    2) Try block must be followed by a Catch block
    3) You need to change your trigger name, it can't be same as any other object in database...

    CREATE TRIGGER  [dbo].[Trigger_RPT_Trans_Audit] ON [RPT_Trans]
    AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
BEGIN
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 
BEGIN CATCH
		PRINT 'Error in Trigger'
END CATCH
END
GO

    - Vishal

    SqlAndMe.com

     
  • Sunday, January 27, 2013 3:28 PM
     
     
    Sign In to Vote
    0
    Also, the INSERTED 'table' will be empty when it is a delete statement that fires the trigger. Could you please try to explain what it is you want to achieve? Adding the 'CREATE TABLE' statements for the tables involved, does not hurt. It would be easier, for me at least, to come up with some code and try to explain to you why I wrote the code the way I did it.
     
  • Sunday, January 27, 2013 3:49 PM
    Moderator
     
     
    Sign In to Vote
    0
    Good point - this trigger needs to be re-written for DELETE action differently.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, January 28, 2013 2:18 AM
     
     
    Sign In to Vote
    0
    Can you tell me how to change the sql so the delete action will work correctly?
     
  • Monday, January 28, 2013 2:21 AM
     
     
    Sign In to Vote
    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:34 AM
     
     Answered
    Sign In to Vote
    0

    Hi,

    In SS management studio, under the relevant table you should see the trigger in that.

    Make sure you have INSERT, UPDATE, or DELETE permissions on the table before creating triggers on that table. Then try to follow the steps to create the trigger.

    Many Thanks & Best Regards, Hua Min


     
  • Monday, January 28, 2013 2:42 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    For Delete action to work correctly you need to use Deleted table, not inserted. I think I posted an outline of the trigger's code in your other thread recently.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked As Answer by wendy elizabeth Wednesday, January 30, 2013 3:28 AM
    •