SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Audit Triggers Not working in SQL Azure		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gtabetnj
Starting Member

USA
8 Posts
Posted - 05/15/2012 :  22:01:30  Show Profile  Reply with Quote
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

India
47069 Posts
Posted - 05/16/2012 :  00:26:52  Show Profile  Reply with Quote
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/
Go to Top of Page

gtabetnj
Starting Member

USA
8 Posts
Posted - 05/16/2012 :  10:40:40  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 05/16/2012 :  20:02:02  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000