edi77
asked on
Trigger question in MS SQL 2005 on update /insert audit
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(DateChang
SELECT GetDate(),'[Samples Received]', suser_sname(),
@Operation
--End of Trigger
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(DateChang
SELECT GetDate(),'[Samples Received]', suser_sname(),
@Operation
--End of Trigger
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
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
ASKER
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?
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?
>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(DateChang
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.
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(DateChang
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.
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.
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.
ASKER
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.
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.
ASKER
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.
ASKER
anyone out there who can help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>>>
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