Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql trigger problem Expand / Collapse
Author
Message
Posted Saturday, January 26, 2013 9:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
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
Posted Saturday, January 26, 2013 10:54 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:38 AM
Points: 386, Visits: 366
Hi,
the trigger is missing a begin catch & end catch block.


--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Post #1412103
Posted Sunday, January 27, 2013 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1412133
Posted Sunday, January 27, 2013 6:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
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
Posted Sunday, January 27, 2013 7:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 3,422, Visits: 5,368
wendy elizabeth (1/27/2013)
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?


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



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1412162
Posted Thursday, April 11, 2013 11:08 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 421, Visits: 1,000
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 »

Add to briefcase

Permissions Expand / Collapse