April 23, 2008 at 3:41 pm
Hello, I'm working on some triggers to log database activities. It's just working fine but try to figure out some easy typing functions. For example:
CREATE TRIGGER a
ON b
AFTER INSERT, UPDATE AS
DECLARE @C
SET @C = (1+1)
BEGIN
INSERT INTO d
(e,f,g)
SELECT
h.i
h.j
h.k
FROM deleted h
INSERT INTO d
(e,f,g)
SELECT etc...
from inserted l
--etc.....
END;
I think I can't make the SELECT any easier because it has some functions like getdate() in it. The INSERT INTO d (e,f,g) function would be less work if it was something like INTO (*), it doesn't work, what should it be?. Probably easy but I got stuck on the subject. Thanks for thinking.
Also,,, Can anybody explain me how to get only the acting on update, insert on rows from column 21, 22 and 30 for example? I tried to read the msdn's but couldn't figure it out.
Enjoy your database.
April 24, 2008 at 12:35 am
[font="Verdana"]
Also,,, Can anybody explain me how to get only the acting on update, insert on rows from column 21, 22 and 30 for example? I tried to read the msdn's but couldn't figure it out.
Create Trigger trg_Sample On {Base Table}
After Insert, Update
As
If Update{Col21} Or Update{Col22} Or Update{Col30}
...
Just add the IF condition into your code so that you can perform action only if either of the columns get updated. The above IF condition is purely for the Update case. I don't know for the Insert.
Hopes some of the experts from SSC will update both of us.:)
Mahesh
[/font]
MH-09-AM-8694
April 24, 2008 at 7:14 am
One thing you can do is:
create trigger dbo.MyTableLog on dbo.MyTable
after insert, update, delete
as
declare @TransactionID uniqueidentifier
select @TransactionID = newid()
if update(MyColumn1)
insert into MyLogDatabase.dbo.MyTable (Col, Val, Act, Trans)
select 'MyColumn1', coalesce(inserted.MyColumn1, deleted.MyColumn1, 'No Value'),
case
when inserted.id is null then 'Del'
when deleted.id is null then 'Ins'
else 'Upd'
end, @TransactionID
from inserted
full outer join deleted
on inserted.id = deleted.id
where inserted.MyColumn1 != deleted.MyColumn1
or (inserted.MyColumn1 is null and deleted.MyColumn1 is not null)
or (deleted.MyColumn1 is null and inserted.MyColumn1 is not null)
if update(MyColumn2)
insert into MyLogDatabase.dbo.MyTable (Col, Val, Act, Trans)
select 'MyColumn2', coalesce(inserted.MyColumn2, deleted.MyColumn2, 'No Value'),
case
when inserted.id is null then 'Del'
when deleted.id is null then 'Ins'
else 'Upd'
end, @TransactionID
from inserted
full outer join deleted
on inserted.id = deleted.id
where inserted.MyColumn2 != deleted.MyColumn2
or (inserted.MyColumn2 is null and inserted.MyColumn2 is not null)
or (deleted.MyColumn2 is null and inserted.MyColumn2 is not null)
.... (the rest of the columns)
Continue for all of the columns you want to log.
That will allow you to log only the columns that actually changed, and only the columns you actually want to track.
For this to work, you need a separate log database with the right tables in it. You can either have one table per table you want to log (which is how I wrote the above trigger), or you can have one log table and have a column for which table the transaction was in.
This type of trigger will tell you whether the action was an insert, a delete, or an update.
The variable @TransactionID is meant to allow you to determine whether two or more log entries were from the same action, or from different actions.
Your log table will, of course, need to have columns like LogDate, which should be DateTime and have a default of Getdate(). You might also want a LogBy column, which would hold the user ID of the person doing the insert/update/delete.
Does that give you what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2008 at 7:20 am
A couple of more general points on database logging:
Using triggers to do logging will make your database slightly slower, since the triggers have to fire for any logged transaction.
Having log tables in your primary database means they will be in your backups too. That means that your primary database will grow more rapidly than otherwise, and your backups and restores will take longer and take more disk/tape space. It will also cause the transaction log on your primary database to grow more rapidly.
Having log tables in a separate database means you should have that database in simple recovery mode, to avoid having its log file grow too rapidly. The database itself will grow rapidly and will end up taking up more space than the primary database, in many/most cases. Be prepared to deal with a rapidly growing database.
The vast majority of logging needs can be more efficiently handled by using a log parsing program and using Full Recovery on your primary database. This method doesn't slow down your database, doesn't use anywhere near as much disk/tape space, and is more accurate and defensible. Unless you are planning on running regular reports against the log, or are planning on setting up "undo" commands for users, use this method. There are several log parsing programs available, including one from RedGate (the owners of this site), ApexSQL, and Lumigent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply