Question on triggers for our audit tables
in [Programming SQL]
|
Prev: Schema comparison of two databases
Next: sproc question
From: Omid Golban on 17 Jul 2008 12:43 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
From: Eric Russell on 17 Jul 2008 13:24 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. "Omid Golban" wrote: > 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
From: Alex Kuznetsov on 17 Jul 2008 13:31 On Jul 17, 11:43 am, Omid Golban <OmidGol...(a)discussions.microsoft.com> wrote: > 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 Did you consider replication - might be easier.
From: Omid Golban on 17 Jul 2008 13:40 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 "Eric Russell" wrote: > 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. > > "Omid Golban" wrote: > > > 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
From: Eric Russell on 17 Jul 2008 13:41
I think he's talking about maintaining a permanent log for each table of all inserts and deletes. "Alex Kuznetsov" wrote: > On Jul 17, 11:43 am, Omid Golban > <OmidGol...(a)discussions.microsoft.com> wrote: > > 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 > > Did you consider replication - might be easier. > |