January 22, 2015 at 6:38 pm
Hi Jeff
Can I try the trigger without modifying my table since they already have a created on and user id columns
Please advise...
January 22, 2015 at 7:00 pm
First, look at your EmployeAudit table. You cannot have two columns named the same.
Look at your Employee table. There is nothing to guarantee that the ChangeUserId will be populated. There's also nothing that does a check on the CreationDateTime to ensure that a nearly correct date and time have been entered.
Also, ChangeUserID implies that something feeding this table is going to record changes in the Employee table. That has nothing to do with who created the first insert. You need an extra column for that. Following your naming convention, the column might be named CreationBy, should be NOT NULL, should default to ORIGINAL_LOGIN(), should be checked to be a least a certain length, and should not be allowed to change once populated. We can actually build the immutability into the trigger, if you'd like. Remember, the initial value of this column must last forever.
CreationDateTime is already NOT NULL but you might want to add a check to it to ensure that someone doesn't try to 1) Enter a date/time that doesn't make sense and 2) a check that makes it impossible to change the value once a date/time has been entered. Remember, that also has to last forever.
If that's too much, then maybe we can change the premise of the audit and only record from the INSERTED table. That will still duplicate data but it might be easier for folks to work with and you wouldn't have to worry so much about making immutable data for the "Creation" columns. That would, of course, also require auditing the final DELETE, which would be a duplication of data. For a small table like an Employee table, that might be alright. I wouldn't do it on large tables, though.
Lemme know.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2015 at 7:55 pm
To add to my suggestions on the Employee table, I just don't understand the need for a "ChangeType" column in the Employee table unless it means something totally different than "I", "U", or "D".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2015 at 8:26 pm
Hi Jeff,
Thanks very much again 🙂
Please give me some time today ... I will thoroughly analyze the full scope of this table and post shortly...
January 24, 2015 at 1:40 am
Hi Jeff,
Sorry for the late reply, I took some time to find out things you asked...
I also come up with doubts regarding the things you said,
Please spare me if you find some of the doubts very silly 😀
There's also nothing that does a check on the "CreationDateTime" column to ensure that a nearly correct date and time have been entered.
I have checked with the application team, "CreationDateTime" always defaults to getDate and it cant be changed manually.
You are right about Change User Id, it checks who made the change and not the original insert user id.
Here are my doubts in order, please clarify it.
RowCount still takes some time and for high hit ratios, it can become a bit obnoxious.
It means when a bulk insert is happening , then it will affect the performance of the database right ..? or do you mean any other scenarios..?
It will prevent the relatively long time it takes to return a rowcount even when it's not used
It means that the row count is 0 right ..?
This will more accurately return who the real user was if any form of "impersonation" comes into play
I know the general meaning of impersonation, but how it is related here ..? Users login with their ID's only right..
This trigger assumes that the Employee table and the EmployeeAudit table have both been modified by adding a
"CreatedOn" DATETIME column that defaults to GETDATE() and a "CreatedBy" column that defaults to ORIGINAL_LOGIN().
Please tell me is it mandatory to modify the Original table to use the new tirgger since it already has "Created on " Column ..?
I am asking this question because this table is there for 15 years from now, If i ask them to add one column, i need to have a strong reason to back me... Please clarify...
Also If any Java online application uses this table in their logic , do they have to recompile their Java code..?
Thats all the doubt I have... Please clarify them... I need to be confident while speaking with my DBA 🙂
This is a very good Service Improvement I can Provide to them.... 🙂
I ran the new trigger without adding extra column in my Table, the trigger worked like it supposed to be but only thing missing is the "Created by " Column...
Do i need to add extra column in the Audit and Original table for sure ..?
Please clarify my doubts ....
Thanks in Advance....
January 24, 2015 at 2:08 am
First, look at your EmployeAudit table. You cannot have two columns named the same.
Im sorry That is My Mistake, I was trying to create the table in test environment and copy pasted that thing here in the post .
It is actually AdjustmentType, AdjustmentReason.......etc .... not Change. ... There is no Change type in the Original Table, all are called Adjustment... Anyway that will not affect anything you advised.
Apologies for it ....
January 24, 2015 at 2:35 am
lawlietdba (1/24/2015)
Hi Jeff,Sorry for the late reply, I took some time to find out things you asked...
I also come up with doubts regarding the things you said,
Please spare me if you find some of the doubts very silly 😀
First to answer your question,There's also nothing that does a check on the "CreationDateTime" column to ensure that a nearly correct date and time have been entered.
I have checked with the application team, "CreationDateTime" always defaults to getDate and it cant be changed manually.
I didn't see such a default in the CREATE TABLE code you supplied. And, yeah, even with a default, it can be changed manually. A simple UPDATE on the column would do the dirty deed. One way to make it much more difficult to change it manually (or by managed code) is to have an AFTER INSERT trigger that would write back the original value if it the original value wasn't null. You could also do that with an INSTEAD OF INSERT trigger to save the computation time of doing the write back but it won't happen that frequently so an INSTEAD OF INSERT trigger would probably be overkill.
You are right about Change User Id, it checks who made the change and not the original insert user id.
Here are my doubts in order, please clarify it.
First Doubt, Reg Row CountRowCount still takes some time and for high hit ratios, it can become a bit obnoxious.
It means when a bulk insert is happening , then it will affect the performance of the database right ..? or do you mean any other scenarios..?
No... quite the opposite for triggers. If a bulk insert occurs, only one rowcount will be returned. If your trigger is handling thousands of single row inserts, the returning a rowcount of "1" anywhere a rowcount would be returned in the trigger can start to add up in the overall scheme of things.
second Doubt, Reg Row Count,It will prevent the relatively long time it takes to return a rowcount even when it's not used
It means that the row count is 0 right ..?
Any rowcount returned from the trigger, whether it's "0" or something else, is a total waste of clock cycles during normal operation because no ones supposed to see anything running from the trigger and usually wouldn't anyway because the GUI or the proc aren't looking for a return from the trigger. The row counts would be calculated, gotten ready for display, and sent to the proverbial bit bucket. Better to just knock all that nonsense out by using SET NOCOUNT ON.
Third Doubt, Reg OriginalLogin()This will more accurately return who the real user was if any form of "impersonation" comes into play
I know the general meaning of impersonation, but how it is related here ..? Users login with their ID's only right..
Correct. Users login with their IDs only. But there are some places where a stored procedure might need to run with elevated privs that you don't want the user to have. For example, you might want to allow a user to execute a store procedure that Truncates a staging table but you don't want to give the user privs (DDL Admin at the very least) to truncate tables. In a case like that, the stored procedure would contain a line like EXECUTE AS OWNER and that means that while that proc is running, it will appear as if "DBO" (or the actual owner of the database, if different) were running the proc (because it is). That's a form of "impersonation". If you use "USER" or some of the other user identifying functions, they'll report as "DBO" or "SA" in this case. If you use ORIGINAL_LOGIN(), it will identify the user that told the proc to run.
Fourth Doubt, Reg New TriggerThis trigger assumes that the Employee table and the EmployeeAudit table have both been modified by adding a
"CreatedOn" DATETIME column that defaults to GETDATE() and a "CreatedBy" column that defaults to ORIGINAL_LOGIN().
Please tell me is it mandatory to modify the Original table to use the new tirgger since it already has "Created on " Column ..?
I am asking this question because this table is there for 15 years from now, If i ask them to add one column, i need to have a strong reason to back me... Please clarify...
Also If any Java online application uses this table in their logic , do they have to recompile their Java code..?
You already have a "CreationDateTime" column and that will work but only if the Java code never changes it once a row had been inserted. You also have to allow for when someone does things not using the Java code. That means that you need to add a default on the table on that column of GETDATE() or CURRENT_TIMESTAMP.
Thats all the doubt I have... Please clarify them... I need to be confident while speaking with my DBA 🙂
This is a very good Service Improvement I can Provide to them.... 🙂
My doubt after running the new triggerI ran the new trigger without adding extra column in my Table, the trigger worked like it supposed to be but only thing missing is the "Created by " Column...
Do i need to add extra column in the Audit and Original table for sure ..?
To avoid the massive duplication in the EmployeeAudit table and still make the original INSERT user available even though INSERTs won't be audited until the row changes and to capture when someone inserts to the Employee table without using the Java code (and it WILL happen), the answer is "YES". You need to add that column and it should/must default to ORIGINAL_LOGIN() on the Employee Table. To be clear, except for an IDENTITY column (if folks have one) on the audit table, there must NOT be any defaults on the columns of the audit table. All the information should come from the DELETED logical table of the audit trigger on the (in this case) Employee table.
Please clarify my doubts ....
Thanks in Advance....
Again and just to convince the DBA, the whole purpose here is to prevent having multiple rows of exact duplicate information in the audit table because of auditing inserts. Auditing inserts immediately causes a row identical what just got inserted into the Employee table to also exist in the EmployeeAudit table. Only the DELETED side of UPDATEs and DELETEs themselves should be audited if you want to avoid that explosion of data. Again, that explosion of data is due to duplicates and will affect the footprint on the disk, time to backup, diskspace for the backups, time to do the tape backup, size on the tape, time to do restores, to do any index maintenance, etc, etc, etc. All of that can be avoided using the trigger I wrote for you and the columns that need to be there.
Now, and like I said before, the Employee table probably won't suffer many changes so the Employee Audit table won't get that big. If they don't actually mind all the exact duplication on what should be a small table and they don't mind the extra disk space and all that goes with it, they can go back to the original triggers or I can write one similar to what I wrote for a small bit of performance increase.
But, don't forget this method. It's what I use when my tables are measured in tens and sometimes hundreds of GigaBytes. Imagine having 2, 3, or 4 times that size in unnecessary duplicated data.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2015 at 3:20 am
Hi Jeff,
Thanks again for clarifying my Doubts.... 🙂
Now, and like I said before, the Employee table probably won't suffer many changes so the Employee Audit table
No Jeff, It suffers many changes daily.
The row count of the main table is : 19 536 851
The row count of the audit table is : 42 50 918
But I dont know how to interpret these values in GB's.
Please tell me by seeing this row count, is my table really very big..?
All of that can be avoided using the trigger I wrote for you and the columns that need to be there.
Please look into the below data, first is for old trigger and the second is for new trigger which you wrote..
ChangeDateChangeUserIdChangeTypeEmployeeNumberSuffixEmployeeTypeCodeAppEffDateCreationDateTimeAppExpDateEmployeeAmountAdjustmentUserIdAdjustmentReasonAdjustmentManagerIdAdjustmentStatusAdjustmentTypeAdjustmentReasonId
2015-01-23 20:21:54.407CFX_ADM U c39686880001995-11-28 00:00:00.0001996-06-21 00:00:00.000NULL12390.00FEEDNULLNULLOTNULL
ChangeDateChangeUserIdChangeTypeEmployeeNumberSuffixEmployeeTypeCodeAppEffDateCreationDateTimeAppExpDateEmployeeAmountAdjustmentUserIdAdjustmentReasonAdjustmentManagerIdAdjustmentStatusAdjustmentTypeAdjustmentReasonId
2015-01-23 20:21:54.407CFX_ADM I c39686880001995-11-28 00:00:00.0001996-06-21 00:00:00.000NULL12390.00FEEDNULLNULLOTNULL
The new trigger works well and eliminates all that duplication now itself. I checked it. Only Updated and deleted records gets into Audit with new trigger without adding new columns.
Please advise...
January 24, 2015 at 3:33 am
Hi Jeff,
Adding to my previous doubt, to be more precise my doubt is.
1. Can I implement the new trigger which you wrote without adding extra column ?
2. If I implement the new trigger which you wrote without adding extra column, will I lose any of the audit functionality which is already provided by the old 3 triggers..?
Please clarify these two doubts...
January 24, 2015 at 4:31 am
Hi Jeff,
Please spare me, im asking same doubt again, :unsure:
Im doing the testing of the new trigger without adding new Column. I found the following .
1. During an Update it inserts the audit table with the previous data, so My first insert will get saved in the audit table whenever it is updated first.
2. After each update on the same record the previous state of the row gets saved in audit.
3. even while deleting,that row gets saved in the Audit table.
So the new trigger works like the old trigger except that it prevents the duplication.
Is your new Trigger misses any functionality of the old triggers if it is been used without adding new column ?
January 24, 2015 at 10:41 am
Let's answer the questions in the reverse order of your posts.
lawlietdba (1/24/2015)
Hi Jeff,Please spare me, im asking same doubt again, :unsure:
Im doing the testing of the new trigger without adding new Column. I found the following .
1. During an Update it inserts the audit table with the previous data, so My first insert will get saved in the audit table whenever it is updated first.
Correct. And if you never update a given row, the first insert for that row will be in the Employee table. That's why we'll need a "reporting view" for reporting that you might want to do.
2. After each update on the same record the previous state of the row gets saved in audit.
Correct. And the ChangeDate will reflect when that previous state was changed, the ChangeUserID will reflect who or what changed the row in the Employee table to make this a previous state, and the ChangeType will indicate if it was an UPDATE or DELETE that caused the change of state because INSERTs aren't audited in the same way.
3. even while deleting,that row gets saved in the Audit table.
Correct.
So the new trigger works like the old trigger except that it prevents the duplication.
Correct. To be specific, the duplication is prevented by never auditing the INSERTED logic table from the trigger.
Is your new Trigger misses any functionality of the old triggers if it is been used without adding new column ?
No. It just prevents duplication (only shows previous states of Employee rows) and runs a bit faster. But, it does change the way that people would have to think about reporting on the audit table as follows.
1. The Employee table becomes part of the audit because if a row is inserted and never changed, only the Employee table will contain information for that EmployeeNumber. The original insert will not become a part of the audit table if a given row is never update. That's why we'll need a (for example) "EmployeeAuditState" or "EmployeeAuditReporting" view to put the Employee and EmployeeAudit table "together" for auditing purposes. THAT's why the CreationDate column must reflect the date of the original insert.
2. People have to remember that the first 3 columns of the EmployeeAudit don't reflect when the information on the EmployeeAudit row became active. Instead, the columns reflect when the information on the audit row became inactive. Put another way that might be easier to think of, the EmployeeAudit table only contains "old" information before changes occurred (previous state) and the Employee table only contains "new" information (current state). For some reason, that blows some folks minds when they're trying to figure out what the "current state" actually was on a any given date.
3. Not adding the new column is OK if the AdjustmentUserID column records the correct information. More on that below.
lawlietdba (1/24/2015)
Hi Jeff,Adding to my previous doubt, to be more precise my doubt is.
1. Can I implement the new trigger which you wrote without adding extra column ?
2. If I implement the new trigger which you wrote without adding extra column, will I lose any of the audit functionality which is already provided by the old 3 triggers..?
Please clarify these two doubts...
Based on the data you posted, I think the answer to #1 above might be "Yes" but it depends on the CreationDate and AdjustmentUserID columns to identify the first insert into the Employee table. The AdjustmentUserID is allowed to be NULL according to your previous CREATE TABLE code. If the value in that column is NULL on the original INSERT for an employee, you'll have no idea who or what did the original insert. The AdjustmentUserID also appears to be a process name rather than a login name. If that's what you want, that's ok but, to cover the eventuality of someone manually inserting an original row, I recommend that you change the Employee table to make the AdjustmentUserID NOT NULL and have a default of ORIGINAL_LOGIN(). That should not have any impact on the Java code or ORM objects/code, if there's any.
While you're making such a change, I'd add a default of GETDATE() to the column definition in the table for the CreationDate column to automatically cover for when people forget to provide a date on original INSERT. I agree that it absolutely must continue as a NOT NULL column.
The answer to question #2 is based on what you do about the AdjustmentUserID. If correct non-null population of that column isn't enforced by changing the column to NOT NULL (at the very least), then the functionality that you will lose is that you won't be able to ever tell who did the original insert for a the given employee. No other functionality will be lost because ALL changes are still audited. We just prevented the rampant duplication of data in the EmployeeAudit table.
Like I said previously, it will require a change in understanding what the data is for people who wrote reports against the audit table previously. If you have such reports in existence already, they will need to be changed to use the new "understanding". Depending on how the new view that stitches the Employee and EmployeeAudit table together, that might be just a table name change in the reporting. If you REALLY want to be sneaky and make it so that none of the reports need to be changed, we could change the name of the EmployeeAudit table to something else, change the one audit trigger to point to that new name, and create the new view to EmployeeAudit so that, depending on how it was written and what it looked for, none of the current reporting code would actually have to change.
As a bit of a sidebar, this is one of those reasons why it's inconvenient, at best, to ever use Hungarian notation to identify a table as a table in the name of a table. You don't have that problem. It's just a sidebar comment to others reading this thread.
lawlietdba (1/24/2015)
Hi Jeff,Thanks again for clarifying my Doubts.... 🙂
Now, and like I said before, the Employee table probably won't suffer many changes so the Employee Audit table
No Jeff, It suffers many changes daily.
The row count of the main table is : 19 536 851
The row count of the audit table is : 42 50 918
But I dont know how to interpret these values in GB's.
Please tell me by seeing this row count, is my table really very big..?
All of that can be avoided using the trigger I wrote for you and the columns that need to be there.
Please look into the below data, first is for old trigger and the second is for new trigger which you wrote..
OLD TRIGGER
ChangeDateChangeUserIdChangeTypeEmployeeNumberSuffixEmployeeTypeCodeAppEffDateCreationDateTimeAppExpDateEmployeeAmountAdjustmentUserIdAdjustmentReasonAdjustmentManagerIdAdjustmentStatusAdjustmentTypeAdjustmentReasonId2015-01-23 20:21:54.407CFX_ADM U c39686880001995-11-28 00:00:00.0001996-06-21 00:00:00.000NULL12390.00FEEDNULLNULLOTNULL
ChangeDateChangeUserIdChangeTypeEmployeeNumberSuffixEmployeeTypeCodeAppEffDateCreationDateTimeAppExpDateEmployeeAmountAdjustmentUserIdAdjustmentReasonAdjustmentManagerIdAdjustmentStatusAdjustmentTypeAdjustmentReasonId2015-01-23 20:21:54.407CFX_ADM I c39686880001995-11-28 00:00:00.0001996-06-21 00:00:00.000NULL12390.00FEEDNULLNULLOTNULL
The new trigger works well and eliminates all that duplication now itself. I checked it. Only Updated and deleted records gets into Audit with new trigger without adding new columns.
Old and new trigger audit data looks similar but with no duplication. The Audit table didnt have who is the user which made the first inser previously also... so why cant we just leave it as it is ... if we care about is duplication of data, then the new trigger which you wrote already does the job in my audit table without modifying it... so can i just use as it is ...?Please advise...
I'm actually a bit confused by part of the post above. You said...
Please look into the below data, first is for old trigger and the second is for new trigger which you wrote..
... but I don't see a "second" unless you're talking about the INSERT you posted and then that would NOT be caused by the new trigger because the new trigger does NOT put INSERTs into the EmployeeAudit table.
The description you provide below that seems to be correct though.
To cut to the chase and as I stated further above in this post, if you change the AdjustmentUserID to NOT NULL and give it a default of ORIGINAL_LOGIN() as a part of the table definition, then you're good to go and no other columns will need to be added. If you don't make those changes, then you run the risk of not being able to identify who or what made the original INSERT but that might be an acceptable risk for your company and the new trigger will still do everything else the old triggers did except for the duplication of data.
As for your stats on how many rows there are in each table, that's actually fairly small for some folks. Especially the EmployeeAudit table. But, it depends. What time span do the number of rows in the EmployeeAudit table represent? If it's for just one hour or even just one day, the EmployeeAudit table is going to become an absolute monster-sized table. If, however, the time span in the EmployeeAudit table represents, say, a year or more of changes, the EmployeeAudit table will continue to be quite small as I stated.
To figure out how much space is actually being used by the Employee and EmployeeAudit table, run the following code...
EXEC sp_spaceused 'dbo.Employee';
EXEC sp_spaceused 'dbo.EmployeeAudit';
To summarize all of the above, things look fine the way they are with the understanding that if you don't make the recommended changes to the AdjustmentUserID column (which won't require any Java or other code changes from what I can currently see), you stand the probably rare chance of not knowing who did the original INSERT for any given employee.
Tell your DBA that a fellow DBA said "Hi" and well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2015 at 12:22 pm
Hi Jeff ,
Thank you very much Jeff ...!!! 🙂
Your explanation clearly clarified all the doubts I had ....
This experience will help me in the long run .....
I will implement all ur suggestions in my environment and let you know the status ...
I will also run the query you mentioned regarding size and post here ....
I have only one doubt now
Inserts don't ever have to be in audit right ..?
Only updated and deleted data needs to be in audit table irrespective of any environment right ...?
January 24, 2015 at 2:53 pm
Correct. Inserts never go into the audit table. Only the changes from UPDATEs and DELETEs. If a row is Inserted into the Employee table, and is never updated, it will only be found in the Employee table. That's why you'll need a reporting view to stitch the Employee and EmployeeAudit table together using UNION ALL.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2015 at 11:56 pm
Hi Jeff ....
Understood 🙂
Thanks very much again ....
January 25, 2015 at 9:39 am
You bet and thank you for the great questions and feedback throughout this thread. Let us know how it all works out when you get the chance.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply