Audit Table Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Audit Table Trigger

Author

Topic

KenS
Starting Member

6 Posts

Posted - 2007-08-24 : 10:37:53

I saw some good threads on how to create the triggers on the "main" table and how to get it to insert rows to the "audit" table. Now I want to protect my audit table. The audit table should never have updates or deletes. Those can be done like this (AccountHistory is the audit table for main table Account):
CREATE TRIGGER [AccountHistory_DTR] ON AccountHistory
FOR DELETE
AS
RAISERROR('Deletes are not permitted on history tables.', 16, 1)
ROLLBACK TRANSACTION
go

It is brutal, but effective.

Now I want to create a trigger that will only allow inserts to the audit table from the trigger of the main table. How can I do this?

Ken

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-24 : 10:58:48

Let me ask you this

Do you have sproc acces only to the data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

KenS
Starting Member

6 Posts

Posted - 2007-08-24 : 11:07:49

That is the plan. There should be no ad-hoc access.

But there is always someone with the access. The idea is to set up a road block so that even the most trusted DBA would have to first remove the trigger before making a change to an audit table. Such a change could not be done by accident.

Plus, there should be no reason to insert a row into the audit table directly.

Also, I got curious if I could do it. Whether it is a good idea or not to do it is another question.

Ken

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-24 : 12:07:49

sounds like a lot of overhead

My goal is usually to keep transaction as short as possible

So if you are in control and no one developers has access except via sprocs, then I think this is overkill

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

KenS
Starting Member

6 Posts

Posted - 2007-08-27 : 11:13:50

I think this would do it. Does this still seem like too much overhead?

CREATE TRIGGER AccountHistory.AccountHistory_ITR ON AccountHistory.AccountHistory
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
-- The only way that inserts are allowed to AccountHistory is when the insert is done via
-- another trigger.
-- Therefore, trigger_nestlevel must be > 1 to allow an insert to AccountHistory.
IF (trigger_nestlevel() <= 1)
BEGIN
RAISERROR('Inserts are not permitted directly to history tables. Inserts can only be done via modifications to the main table', 16, 1)
ROLLBACK TRANSACTION
END
END
GO

Ken

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-27 : 12:58:54

no not really, but this isn't really bullet proof also

nice concept though!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources