sql trigger problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » sql trigger problem

sql trigger problem

Rate Topic

Display Mode

Topic Options

Author

Message

wendy elizabeth

Posted Saturday, January 26, 2013 9:38 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159

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

Post #1412099

db4breakfast

Posted Saturday, January 26, 2013 10:54 PM

Old Hand

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:42 AM
Points: 374, Visits: 326

Hi,
the trigger is missing a begin catch & end catch block.

--------------------------------------------------------------------------------------
Hai Ton
My DB4BREAKFAST blog: http://db4breakfast.blogspot.com

Post #1412103

opc.three

Posted Sunday, January 27, 2013 9:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

You were also missing the END that matched the opening BEGIN. If you do not plan on doing any custom error handling, e.g. enriching the error message, in the trigger then I would recommend completely removing the TRY/CATCH and go with something simple like this:

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]
            (
             Package_ID,
             Received_Date,
             Download_Date
                
            )
            SELECT  Package_ID,
                    Received_Date,
                    Download_Date
            FROM    INSERTED;
END
GO

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1412133

wendy elizabeth

Posted Sunday, January 27, 2013 6:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159

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?

Post #1412154

dwain.c

Posted Sunday, January 27, 2013 7:29 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,370, Visits: 3,250

wendy elizabeth (1/27/2013)

Try expanding the table and you'll see a TRIGGERS branch there. It will be under that.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1412162

mmartin1

Posted Thursday, April 11, 2013 11:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:02 PM
Points: 198, Visits: 656

I'm sure this is not the case, but thought I should mention: make certain the ID column in your audit table is not a identity column.

Post #1441386

« Prev Topic | Next Topic »

Permissions