Solved

Trigger question in MS SQL 2005 on update /insert audit

Posted on 2007-11-28
9
4,492 Views
Last Modified: 2008-02-13
I have created this trigger below to audit updates and inserts to my table, I also want any updates or inserts to be copied to another table called labdata, and then logged in my audit table. how would i do this?
this is what i have so far and it works for what i have so far.
CREATE TRIGGER AuditSamples
ON dbo.[Samples Received]
AFTER INSERT, UPDATE
--NOT FOR REPLICATION
AS
DECLARE @Operation char(6)

IF EXISTS(SELECT * FROM deleted)
      SET @Operation = 'Update'
ELSE
      SET @Operation ='Insert'

INSERT INTO dbo.SamplesAudit(DateChanged,TableName,UserName,Operation)
SELECT GetDate(),'[Samples Received]', suser_sname(),
      @Operation
--End of Trigger
0
Comment
Question by:edi77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:pai_prasad
ID: 20369472
I also want any updates or inserts to be copied to another table called labdata,
>>>>
is my understanding correct

1. Insert happens in the parent table
2. Trigger is fired
3, U need the inserted row in child table
4 make an audit entry for the insert in audit table

0
 
LVL 42

Expert Comment

by:dqmq
ID: 20369486
Not quite sure what your question is.  To insert in another table, add another insert to the trigger.  

Note that your code only inserts one row in the audit table, even if your update affects several rows in the samples_received.  That may or may not be what you intend for the audit table.  But for the labdata table, it's not acceptable.

For that case, you need to join the the Inserted table to generate multiple rows:

INSERT INTO dbo.labdata
    Select * from Inserted
0
 

Author Comment

by:edi77
ID: 20369628
ok my insert and updates should only happen one at a time.
yes , after i insert any record into lab samples i also want to insert it into the labdata table.
how where in the code do i do that?
can u show me what u mean?

after it does that for insert or update, i would like it to go to the audit table as well.

does that make sense?

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 42

Expert Comment

by:dqmq
ID: 20370402
>ok my insert and updates should only happen one at a time.

I've heard that before!!



CREATE TRIGGER AuditSamples
ON dbo.[Samples Received]
AFTER INSERT, UPDATE
--NOT FOR REPLICATION
AS
BEGIN --Trigger

--insert one row in samples for each update or insert STATEMENT
--to make a ROW trigger, remove the DISTINCT keyword
INSERT INTO dbo.SamplesAudit(DateChanged,TableName,UserName,Operation)
SELECT DISTINCT GetDate(),'[Samples Received]', suser_sname()
, ISNULL('Update','Insert')  From Deleted

--insert one row in lab data for each update or insert ROW
INSERT INTO dbo.labdata
   SELECT * FROM Inserted

END --Trigger


If you want to audit the insert to labdata, then it's best to have a insert trigger on that table, as well.
0
 
LVL 5

Expert Comment

by:ursangel
ID: 20371555
All the experts commenst should work fine,
Just add this line of insert in your existing tirgger.
Insert into dbo.labdata (select * from inserted)
hoping that your lab data and lab sample have the same schema.
0
 

Author Comment

by:edi77
ID: 20371630
ok that didnt really work it said it couldnt find the labdata table ...
the thing is the first way i did it worked so that part is good. the audit on samples recieved works fine.

so if i want to keep it simpler

what if i just did all the inserting into the labdata table in a seperate trigger. would that be better?
if so how would that look?

i want it to say.
when a new record is updated or inserted in samples recieved enter a record into labdata, then audit that record in a an audit table.
actually i dont want to copy the WHOLE record just 3 fields. DNA, Kit, Participant

could you help me with what that syntax looks like. can i write a trigger on one table based on another like this?
i have no idea never done it.
0
 

Author Comment

by:edi77
ID: 20371668
no the field names nor schema arent exactly the same. i only need about 3 fields to be copied into the lab data table from the samples table.
0
 

Author Comment

by:edi77
ID: 20371697
anyone out there who can help?
0
 
LVL 5

Accepted Solution

by:
ursangel earned 500 total points
ID: 20381024
You havent specified which are the column name that u need to be inserted into the LABDATA table.
Im just writing this from my guess. Replace the column name with the exact one's
Just select the coulmn name alone and insert into the labdata
insert into LABDATA (Col1, Col2, Col3) select Col1, Col2 Col3 from Inserted

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 82
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
invoke-sqlcmd help 5 34
How can I group this data in the following manner? 2 33
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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