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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 297, Visits: 339
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: Tuesday, April 08, 2014 12:30 AM
Points: 382, Visits: 344
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: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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
Posted Sunday, January 27, 2013 6:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 297, Visits: 339
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 @ 6:03 PM
Points: 3,590, Visits: 5,098
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 Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
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