 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / SQL / July 2008Question on triggers for our audit tables
We have several versions of our database: OurDatabaseDev OurDatabaseQA OurDatabaseUAT OurDatabase (Production) Furthermore, we have separated our audit tables in separate databases: OurDatabaseDevAudit OurDatabaseQAAudit OurDatabaseUATAudit OurDatabaseAudit (Production) We have insert, update, and delete triggers capturing data into these audit databases. This means all of the triggers will be marked as different in a schema comparison. For example the audit triggers in database OurDatabaseDev will be writing audit data into OurDatabaseDevAudit, and OurDatabaseQA will write audit data into OurDatabaseQAAudit: Dev: CREATE TRIGGER trigger_SomeTable_Update ... INSERT [OurDatabaseDevAudit].dbo.[SomeTable] ... QA: CREATE TRIGGER trigger_SomeTable_Update ... INSERT [OurDatabaseQAAudit].dbo.[SomeTable] ... Is there a good way to write these audit triggers so that they look the same across different databases? Thank you, Omid Are all these databases on the same server? One of the systems I'm currently fostering uses trigger based audit tables just like what you're described. However, we have seperate Dev, QA, PreProd, and Prod servers, so the database names are consistent, and each database on a specific server inserts into a History database with tables split across multiple file groups. If you name your databases differently, for the Dev or QA version, and you want to use distributed queries or inserts, then you have no choice but to deploy different versions of the scripts that code the database name differently. I assume (and hope) your Production databases are hosted on a dedicated production server, but if Dev and QA are on the same server, then consider hosting them on seperate SQL Server instances. That would prevent you from having various versions of the same database and will allow you to deploy the same scripts to each. > We have several versions of our database: > OurDatabaseDev [quoted text clipped - 29 lines] > Thank you, > Omid Production is on its own server. Dev, QA, and Pre-Prod are currently on same server and will be on separate servers early next year. However, we have different teams working on various projects and we end up with many versions of our database: OurDatabaseProj1Dev OurDatabaseProj2Dev OurDatabaseProj1QA OurDatabaseProj2QA OurDatabaseProj1UAT OurDatabaseProj2UAT and their audit databases: OurDatabaseProj1DevAudit OurDatabaseProj2DevAudit OurDatabaseProj1QAAudit OurDatabaseProj2QAAudit OurDatabaseProj1UATAudit OurDatabaseProj2UATAudit Plus we are going to have our ongoing maintenance, hence the databases: OurDatabaseDev OurDatabaseQA OurDatabaseUAT and their audit databases: OurDatabaseDevAudit OurDatabaseQAAudit OurDatabaseUATAudit So this will be a continuing problem. Using dynamic SQL in triggers is not an option for us. It sounds like there is no clean solution to this problem. Thank you, Omid > Are all these databases on the same server? > One of the systems I'm currently fostering uses trigger based audit tables [quoted text clipped - 44 lines] > > Thank you, > > Omid On Jul 17, 12:40 pm, Omid Golban <OmidGol...@discussions.microsoft.com> wrote: > Production is on its own server. > Dev, QA, and Pre-Prod are currently on same server and will be on separate [quoted text clipped - 77 lines] > > > Thank you, > > > Omid what makes you think that replication is not a clean solution? ... snip ... > what makes you think that replication is not a clean solution? I was under the impression that replication comes in handy in synchronizing DML changes not DDL changes. I don't want to synchronize all the DML changes across several databases. Rather I am looking to selectively synchronize some (not all) of DML changes across these databases. i.e. I am looking for an alternative to writing our triggers and hide the database name in the syntax. Something similar to: CREATE TRIGGER trigger_SomeTable_Update ... set @DatabaseName = 'OurDatabase' if db_name() = 'OurDatabaseDev' set @DatabaseName = @DatabaseName + 'Dev' else if db_name() = 'OurDatabaseQA' set @DatabaseName = @DatabaseName + 'QA' ... set @DatabaseName = @DatabaseName + 'Audit' set @DestinationTable = @DatabaseName+'.dbo.' + ... INSERT @DestinationTable (...) values (...) ... On Jul 17, 2:00 pm, Omid Golban <OmidGol...@discussions.microsoft.com> wrote: > ... snip ... > [quoted text clipped - 18 lines] > INSERT @DestinationTable (...) values (...) > ... replication can do it. Alternatively you can generate static-SQL-only triggers, different one for different database. Replication is for synchronizing data between two databases. What he's trying to do is implement row version auditing, meaning that every insert, update, delete into the table is retained for auditing purposes. For example: insert into History.dbo.Customer (ModDate, ModType, CustomerID, CustomerName, ...) select getutcdate(), 'I', CustomerID, CustomerName, ... from INSERTED > On Jul 17, 12:40 pm, Omid Golban > <OmidGol...@discussions.microsoft.com> wrote: [quoted text clipped - 81 lines] > > what makes you think that replication is not a clean solution? Partitioned Views can be used to abstract inserts into multiple tables. For example, if ServerName were part of the partition key, and you insert into the view and specify @@servername as a column in the insert. That would make the trigger deployable across Dev, QA, and UAT. On Production, the triggers could be also deployed withtout modification, but the views would need to be modified, because there is only one version of the database to reference... On second thought, don't go there; just have alternate versions of the triggers for Dev, QA, UAT, and Production. You only have to modify one line of code in each trigger to change the name of the target database. > Production is on its own server. > Dev, QA, and Pre-Prod are currently on same server and will be on separate [quoted text clipped - 77 lines] > > > Thank you, > > > Omid On Jul 17, 11:43 am, Omid Golban <OmidGol...@discussions.microsoft.com> wrote: > We have several versions of our database: > OurDatabaseDev [quoted text clipped - 29 lines] > Thank you, > Omid Did you consider replication - might be easier. I think he's talking about maintaining a permanent log for each table of all inserts and deletes. > On Jul 17, 11:43 am, Omid Golban > <OmidGol...@discussions.microsoft.com> wrote: [quoted text clipped - 33 lines] > > Did you consider replication - might be easier. I currently have a very similar situation, where Dev, QA, and Prod are on the same server. I solved it by creating synonyms in each database that point to the tables in the corresponding audit database. The synonyms are named the same, so any code that references them can be the same in all databases. HTH Vern Rabe > We have several versions of our database: > OurDatabaseDev [quoted text clipped - 29 lines] > Thank you, > Omid This sounds like it would work for me. Would you please show me where I can get to the documentation for creating these synonyms? Thank you, Omid > I currently have a very similar situation, where Dev, QA, and Prod are on the > same server. I solved it by creating synonyms in each database that point to [quoted text clipped - 37 lines] > > Thank you, > > Omid Books Online has the syntax, or you can use Management Studio, right click Synonyms (under each database), select New Synonym..., and after entering the information, either click OK to create it, or click Script to have the create script copied to a query window Vern Rabe > This sounds like it would work for me. Would you please show me where I can > get to the documentation for creating these synonyms? [quoted text clipped - 43 lines] > > > Thank you, > > > Omid Synonyms would be a good solution to the specific problem of redirecting your triggers inserts into the correct database table. I like that better than my previous half suggestion of abstracting the table using a view. However, keep in mind that when generating deployment scripts, the synonym objects will need to be included and customized for Dev, QA, and Prod too. I still think that it would be best to host your Dev and QA databases on seperate SQL Server instances (if not seperate servers) so there will be no need for database renaming. If there is a special situation where QA needs full access to the server's CPU and memory in order to simulate Production and reproduce a performance issue, then you can temporarily shutdown the Dev service. > This sounds like it would work for me. Would you please show me where I can > get to the documentation for creating these synonyms? [quoted text clipped - 43 lines] > > > Thank you, > > > Omid |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.