Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

10774: trigger DML o insert

Posted on 2014-10-17
1
Medium Priority
?
283 Views
Last Modified: 2014-10-17
Suppose I have the following procedure:

Create procedure PA_ActualizarTabla1
Campo_Nuevo Varchar (10)
as
Update Table1
September Field = @ Campo_Nuevo
where id = 1

I want to store in a table of audits before and after registration,
then my question is, which is faster ?,
Add 1 insert statements in this procedure to save the before and after in the table
2 Adding a DML trigger on that table
0
Comment
Question by:enrique_aeo
1 Comment
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40387383
For starters, your proc has an error, as the space in the name [September Field] forces the use of square brackets [] around the name.   Also there's a space after the @.

Not abundantly certain which is faster, but Adding a DML trigger would be the more accurate,

There may be better ways based on your audit table, but give this a whirl..
CREATE TRIGGER tg_upd_PA_ActualizarTabla1
AFTER UPDATE AS

Declare @dt datetime = GETDATE()

INSERT INTO your_audit_table (Col1, col2, col3, action, timestamp) 
SELECT col1, col2, col3, 'new', @dt
FROM inserted
UNION
SELECT col1, col2, col3, 'old', @dt
FROM deleted
GO

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question