November 30, 2010 at 12:32 am
Before moving ahead let me clarify one thing I posted this question in this section caz my db design is done but I need help in deigning the strategy for writing T-SQL.
Scenario
One of the our business requirement has Auditing requirement. Auditing on tables. As per the requirement we have Parent and dependant screen where the AdminUser can select the parent & dependant.
To illustrate this scenario I have attached the dummy design for reference.
Data Structure.GIF - This is the main database structure which stores the business.
Audit Structure.GIF - This structure supports to store the auditing requirements.
I am not going in-depth to explain the data structure.
On UI we have Primary and Secondary entities. Primary entities shows the all the main tables and when AdminUser select the Primary the Secondary shows the dependant tables (in hierarchy).
E.g.
If the AdminUser select Primary - Customer, the Secondary selection available could be Customer_Orders, Order_Items, Customer_Addresses
AdminUser can configure - Customer.date_became_customer to audit if any change (I/U/D) happens on this column the other information like Order_Items.product_id and Customer_Addresses.date_address_to should be capture as supporting information in Audit Transaction Result tables.
the Audit configuration is stored in some tables such as PrimaryConfig, SecondaryConfig.
We decided to proceed with the triggering approach for each config, but we stuck at navigation point.
How can we determine the navigation from Source to target? i.e. Customer -> Order_Items
the problem here is system only stores the required information and not the navigation info...
Let me know if more clarification required...
Please share your thoughts.
Thanks in advance.
Abhijit - http://abhijitmore.wordpress.com
November 30, 2010 at 12:02 pm
I think more clarification is required...
Are you looking for help writing code to generate your triggers or are you looking for how to write the triggers themselves?
If it's the triggers, it would be helpful to look at a particular set of tables and we can walk through the process with that set before generalizing. right now I'm not sure what you mean by the navigation point or how the supporting information that needs to be captured is configured (automatically or manually).
-a.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
November 30, 2010 at 12:13 pm
I wouldn't bother with this level of complexity. Audit the tables individually. You can use timestamps and the usual Join criteria to audit related changes in related tables.
If someone has a relationship between Customers and Orders, and you audit Customers, and audit Orders, that will cover what you need. Right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2010 at 2:40 am
weitzera:
I need construction logic to write the code for trigger. The problem here is everything is dynamic. When the user configures the audit Primary & Secondary, something (SP / some script) should set off to create the Trigger on Primary.
The basic logic here is…
•Create a trigger on Primary for Primary audit is configuration
•Write the code in trigger to capture the Secondary information. This would be navigation to nth level
E.g. Primary Configuration – Customer.date_became_customer
Secondary Configuration – Order_Items.product_id, Customer_Addresses.date_address_to
The trigger should be created on Primary – Customer for any change (I/U/D) on date_became_customer should capture the information Old Value and New Value in addition to this the trigger should also capture the Secondary information (supporting) for Order_Items.product_id, Customer_Addresses.date_address_to for that Customer.
GSquared:
We cannot create the triggers on individual tables. That is not meeting our business requirement. We need to capture the Primary & Secondary information at point in time.
The problem where I stuck is dynamic navigation from point A to point B, as you can see in this case to reach Order_Item I'll have travel through Customer, Customer_Orders & so on.
In this case I have only point A & point B information. there could be the chances that on point B some other entities would be dependant. how could I figure it out nth level will direct me towards point B?
Abhijit - http://abhijitmore.wordpress.com
December 1, 2010 at 6:35 am
You'll have to have something that tells the code which tables are related to which other tables, and how. You could store that as an XML structure, or pass it as an XML parameter to a stored procedure, which would probably be the easiest way to do this.
Once you have that data, you can create a proc to dynamically build the trigger. It's going to take a fair amount of work to create something this complicated, and the error-handling alone is going to be an interesting project.
If you're not comfortable with this yourself, I highly recommend hiring a contractor who already has the needed skillset for this kind of thing. They're going to need to be comfortable with querying database metadata in order to build complex dynamic SQL.
Part of the problem is that something like this will, by definition, be creating untested code in your production database. The moment someone designates a table as Primary and another as Secondary, you're going to have an automatic system build triggers dynamically that, if they have any sort of problem at all, could block all data modification in one or more tables, and could cause all kinds of "interesting" (as in "fire the DBA" = "interesting") error scenarios. By the very nature of it, every one of those triggers will be, essentially, untested code that hasn't gone through any sort of QA, load-testing, suitability testing, et al, and will be introduced directly into your production database in that state and taken live without any review or sign-off.
If you really want to be in that position, you need to make sure before you implement this system, that the resulting triggers will be ROCK-solid, will have a significant level of error handling and fault-tolerance, that any table modifications in dev (where the triggers won't exist) won't have any chance whatsoever of breaking the triggers in production when taken live, and so on. That means a VERY high level of coding skill, expertise, and a very strong understanding of the potential results from something like this.
This is not something you'll want to bang out after a brief discussion on a forum. It's something that should be done with a much greater degree of attention to detail than that will afford you.
Hence, I seriously recommend hiring a top-notch contractor for this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2010 at 12:02 pm
I agree with GSquared. Actually, this is a case where I would make very sure that the actual business requirement is the same as what you have on paper. For example, is the user allowed to select any column in the database as a secondary? What if there are more than one relationship between two columns? How will your trigger-creation code determine which one to use? In the example you gave, suppose someone later creates a table holding products a customer might like based on past products they've purchased. this table might have a foreign key back to the order_items that gave rise to the recommendation. How would your code know that the user is selecting all the items the customer has ever ordered, and not all the items the customer has ever ordered multiplied by all the items that customer might like based on their past orders. Second example, what if someone selects something stupid like address as primary and product_id as secondary? Whose fault will it be if the system becomes unresponsive as a result?
That said, the specific answer to your question is that you can get all the information you need from querying information_schema.tables, information_schema.table_constraints, information_schema.key_column_usage, and information_schema.referential_constraints
The data there will enable you to build a network of which tables have foreign keys to which other tables, and what the foreign key and primary key columns are. You can search that network to find paths between point a and point b (an np-hard problem if you care about the shortest path) Note that you have to treat each link as non-directional, because you could be going from the parent to the child or visa versa.
Clearly this is a non-trivial problem, but one that can be done, especially for a simple data model like yours. However, I don't think it's what you [your users] really want.
I'm sorry if we've come across as patronizing, We're trying to point out some looming pitfalls you might not have seen.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
December 2, 2010 at 8:33 am
Hello,
I once created a general changed data logging mechanism and created dynamic triggers for that solution.
You can check the article Change Data Capture Structure for MS SQL Server 2005
There you might find sample codes.
The idea is read system views to get required column data and use dynamic sql for sp_executesql statements.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply