Audit Triggers Not working in SQL Azure

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

General SQL Server Forums

Database Design and Application Architecture

Audit Triggers Not working in SQL Azure

Author

Topic

gtabetnj
Starting Member

8 Posts

Posted - 2012-05-15 : 22:01:30

I have done Auditing triggers (on insert, update and delete statements, causing insert of a row into an audit table) on tables in SQL 2005, 2008, and 2012, but the same triggers dont seem to work in SQL azure.

Why?

In the past you simply 'select xxx from Inserted', 'Select xxx from Deleted', etc., then do an insert on tblAudit. But it fails on SQL azure

HInts or code that works wopuld be appreciated

Thanks

gej

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 00:26:52

Using triggers in Azure has some limitations Make sure you read this

http://msdn.microsoft.com/en-us/library/windowsazure/ee336242.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gtabetnj
Starting Member

8 Posts

Posted - 2012-05-16 : 10:40:40

NO good - the problem is not creating the Trigger, the problem is getting the trigger to retrieve info from 'Inserted' or from 'Deleted' or from 'Updated' (depending on whether the trigger initiation was from an insert, update, or delete statement) and then to take that info and insert it as a field into an audit table.

In SQL 2000, 2005, 2008, 2012, or in Oracle or DB2 or others the basic syntax is..

Declare @InsertedId as BigInt
Set @InsertedId = Select sysId from Inserted

Insert Into tblAudit Values 'Insert', @insertedId, Current_DateTime)

But this doesnt work in Azure - I simply go into table editting mode and insert a row and it fails

It looks like Azure doesnt populate the 'Inserted' or 'Updated' or 'Deleted' special trigger data values with the changed row content like other database unless you do something like

Update tblXXX set fldname='New Name' where fldSize >10 Onto Updated

with something like hte Onto clause at the end or something - but I cant find a good example of it working, and this seems to eliminated auditing of changes doen from simple table editting, it would all have to be done from an application to get auditted, since the table editting does not include the last Onto clause

This seems overly complicated 'Feature' for something that has worked smoothly in databases for 20+ years

gej

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 20:02:02

there's one issue with you mode of implementation
the above code will work only if you do single row dml operations because you're assuming that inserted will have only one record at a time
In real scenario it can have multiple reords especially when its a batch operation. So you cant you variables to hold inserted table values. it should be table variable or temporary table. so above statement will become


Insert Into tblAudit 
select 'Insert', insertedId, Getdate()
from inserted

Also there's no updated table, it has only inserted and deleted
update is done as combination of delete followed by insert with old values in deleted and new values in inserted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources