Hi
I am trying to produce an update trigger. I understand the concept of
delete and insert triggers without a problem. Unfortuantely, the
update triggers do not have particularly simple documentation in BoL.
So, can someone please explain to me, quite simply how I would produce
a trigger on the following:
I have table 1 which we'll call simon. In here are various columns and
rows. I also have table 2, called simon_a, my audit table.
Whenever anything is updated or deleted in simon, I want it sent to
the simon_a table. Delete, as above, is fine since it's conceptual but
help me out on the update one. I cannot seem to figure out how to get
the information from the table before it's updated.
As ever, champagne and beer for the successful answer.
With thanks
Simon 5 2561
Hi
The before image of your record(s) are held in the deleted "table"
The example "E. Use COLUMNS_UPDATED" in the "CREATE TRIGGER" topic in Books
Online shows a typical auding type trigger http://msdn.microsoft.com/library/de...asp?frame=true
John
"Simon" <aa*****@the-mdu.com> wrote in message
news:f5*************************@posting.google.co m... Hi
I am trying to produce an update trigger. I understand the concept of delete and insert triggers without a problem. Unfortuantely, the update triggers do not have particularly simple documentation in BoL.
So, can someone please explain to me, quite simply how I would produce a trigger on the following:
I have table 1 which we'll call simon. In here are various columns and rows. I also have table 2, called simon_a, my audit table.
Whenever anything is updated or deleted in simon, I want it sent to the simon_a table. Delete, as above, is fine since it's conceptual but help me out on the update one. I cannot seem to figure out how to get the information from the table before it's updated.
As ever, champagne and beer for the successful answer.
With thanks
Simon
That's fine for specific columns which might be updated, but how is it
that you specify for all columns and insert the old data into the audit
table.
Simon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! aa*****@the-mdu.com (Simon) wrote in message news:<f5*************************@posting.google.c om>... Hi
I am trying to produce an update trigger. I understand the concept of delete and insert triggers without a problem. Unfortuantely, the update triggers do not have particularly simple documentation in BoL.
So, can someone please explain to me, quite simply how I would produce a trigger on the following:
I have table 1 which we'll call simon. In here are various columns and rows. I also have table 2, called simon_a, my audit table.
Whenever anything is updated or deleted in simon, I want it sent to the simon_a table. Delete, as above, is fine since it's conceptual but help me out on the update one. I cannot seem to figure out how to get the information from the table before it's updated.
As ever, champagne and beer for the successful answer.
With thanks
Simon
In an update trigger, the deleted table has the original rows, and the
inserted table has the modified rows:
create trigger tru_simon
on dbo.simon
for update
as
begin
insert into dbo.simon_a (col1, col2, audit_action)
select col1, col2, 'update - before image'
from #deleted
insert into dbo.simon_a (col1, col2, audit_action)
select col1, col2, 'update - after image'
from #inserted
end
See "Using the inserted and deleted Tables" in Books Online.
Simon
Hi
The example without the line
IF (COLUMNS_UPDATED() & 14) > 0
will do this.
John
"Simon Aarons" <aa*****@the-mdu.com> wrote in message
news:3f***********************@news.frii.net... That's fine for specific columns which might be updated, but how is it that you specify for all columns and insert the old data into the audit table.
Simon
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! aa*****@the-mdu.com (Simon) wrote in message news:<f5*************************@posting.google.c om>... Hi
I am trying to produce an update trigger. I understand the concept of delete and insert triggers without a problem. Unfortuantely, the update triggers do not have particularly simple documentation in BoL.
So, can someone please explain to me, quite simply how I would produce a trigger on the following:
I have table 1 which we'll call simon. In here are various columns and rows. I also have table 2, called simon_a, my audit table.
Whenever anything is updated or deleted in simon, I want it sent to the simon_a table. Delete, as above, is fine since it's conceptual but help me out on the update one. I cannot seem to figure out how to get the information from the table before it's updated.
As ever, champagne and beer for the successful answer.
With thanks
Simon
Triggers create two tables; INSERTED and DELETED. The deleted table
is the before image, the inserted table is the after image. On insert
or delete triggers only one table is populated, but with the update
trigger you have the old info (deleted) and new info (inserted) in
their respective tables.
Send the info from the deleted table to simon_a in either case
(updating or deleting). This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Hank |
last post by:
I have two SQL Server 2000 machines (server_A and server_B). I've
used sp_addlinkedserver to link them both, the link seems to behave
fine. I can execute remote queries and do all types of neat...
|
by: raulgz |
last post by:
I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..
|
by: anjana sisodia via SQLMonster.com |
last post by:
I have an area on our website where users can customize what data fields they
want to see displayed from a list of about 50 different fields. I have a
query that has about 50 subqueries to pull...
|
by: Jeff Magouirk |
last post by:
Dear Group,
I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.
Say I have a table with
...
|
by: Bruno Lavoie |
last post by:
Hello,
i'm etablishing a naming convention for a new project under
postgresql. For tables, sequences, views, that's ok! I used good
naming conventions for this in the past and i'll keep these...
|
by: Anthony Paul |
last post by:
Hello everyone,
I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be...
|
by: debasisdas |
last post by:
trigger sample code Ex#10
=======================
INSTEAD OF TRIGGER
----------------------------------------
create or replace trigger mytrig instead of delete or insert or update on eview...
|
by: satchi |
last post by:
Ok this should be a simple question but it's seemingly difficult (or something's wrong w/ my SQL Server Managmenet Sudio). I have created new triggers in SQL Server 2000 by clicking on Managing...
|
by: debasisdas |
last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful.
TRIGGERS:
===============
Database trigger is a PL/SQL block that is...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |