473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Triggers - It cannot be this difficult

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
Jul 20 '05 #1
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

Jul 20 '05 #2
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!
Jul 20 '05 #3
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
Jul 20 '05 #4
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!

Jul 20 '05 #5
jmj
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).
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
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...
11
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..
1
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...
1
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 ...
0
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...
11
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...
0
debasisdas
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...
3
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...
0
debasisdas
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...
0
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.