Solved

10774: trigger DML o insert

Posted on 2014-10-17
1
251 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 65

Accepted Solution

by:
Jim Horn earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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