Show Changes Between Records In Main Table And Audit Table
Aug 2, 2006
Hallo
i have two tables, MainTable and MainTableAudit: the second one keeps DML auditing via triggers.
I'm trying to build a query to highlight changes occurred to fields between the MainTable record and its audited records in MainTableAudit, for example, let's suppose i entered an item with some wrong attributes, and edited it three times:
MainTable record contains the latest and current version
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
MainTableAudit contains edited ID002 versions
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|232 pages|2006-07-08 08:32:12
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|212 pages|2006-05-08 10:54:02
ID002|Hitchhikers Guide to Galaxy|Sci-fi|222 pages|2006-07-04 11:42:16
I would like to build a report like this:
first insertion: Hitchhikers Guide to Galaxy|Sci-fi|222 pages
modified on 2006-07-04 11:42:16: field "Title" changed from "Hitchhikers Guide to Galaxy" to "Hitchhikers Guide to the Galaxy", field "PageNo" changed from "222" to "212"
modified on 2006-05-08 10:54:02: field "PageNo" changed from "212" to "232"
modified on 2006-07-08 08:32:12: field "PageNo" changed from "232" to "240"
current version: Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
i'd prefer to use T-SQL and keep all into a single place (a view or storedprocedure), or at least to use reporting services; btw i would like to avoid coding web pages or hosted applications.
I am using the default ASPNETDB.MDF database for a project in SSE 2005 and VWD 2005. I need to use the UserName field from the aspnet_Users table as a foreign key in another table I have in the database. I am doing this so that I can grab data generated from additional fields that I'm adding to the membership registration wizard. However, I am obviously missing some steps since the user name doesn't show up in my second table. What should I do besides creating a relationship between the two fields and tables? Should I be writing some sort of SQL statement to accomplish this?
I want to recursively select all records within a hierarchy, using the main parentid and a textvalue on level 1 OR level 2 of the subcategories.
My data:
CREATE TABLE [dbo].[articlegroups]( [id] [int] NOT NULL, [parentid] [int] NOT NULL, [catlevel] [tinyint] NOT NULL, [slug_en] [nvarchar](50) NOT NULL, CONSTRAINT [PK_globos_articlegroups] PRIMARY KEY CLUSTERED
[Code] ...
When selecting rows I always have the main parentId (so catlevel 0) and the slug_en value.
In my example case I have id 129 and slug_en='cradles'.
I want my query to then return:
idparentidcatlevel 12900 1301291 1361302If I have id 129 and slug_en='pillows'.
I want my query to then return:
idparentidcatlevel 12900 1391291
How can I do this? I'm new to SQL Server. I was reading here [URL] .... on recursive SQL, but how to implement this as I just have one table and I also have 2 selection criteria (main category id and a text value on either level 1 or 2).
How to delete records from multiple tables if main table’s entity is deleted as constraints is applied on all..There is this main table called Organization or TblOrganization.and this organization have branches which are in Brach table called tblBranch and this branch have multiple applications let say tblApplication and these application are used by multiple users called tblUsers.What I want is: when I delete the Organization All branches, application and users related to it must be deleted also.How I can apply that on a button click in asp.net web forms..Right now this is my delete function which is very simple
Public void Delete(int? id){ var str=”DELETE FROM tblOrganization WHERE organizationId=”+ id ; } And My tables LOOK LIKE this CREATE TABLE tblOrganization ( OrganizationId int, OrganizationName varchar(255)
I have two tables, Employee and Calls. They are joined on an Employee field. In my where clause I have a value specified that only returns specific calls. What I would like to have happen is to have the query return all Employee records regardless if any records from the Calls table is present for that employee. I want something that looks like this:Employee # of Calls Employee A 5 Employee B 0 Employee C 10
When I apply a WHERE clause to the Calls table I get this:Employee # of Calls Employee A 5 Employee C 10
I tried a LEFT OUTER JOIN without success. Any suggestions?
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER] group by SSN, name4 having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT 123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
What is the best way to transfer data from the staging table into the main table.
Example: Staging Table Name: TableA_satge (# of rows - millions) Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing: - Load data into staging table (TableA_stage) - Remove any duplication of rows from the staging table (TableA_stage) - Disable all indexes on main table (TableA_main) - Insert into main table (TableA_main) from staging table (TableA_stage) - Remove any duplication of rows from the main table using CTE (TableA_main) - Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
Here's my problem. I have a subreport that shows data and labels if data is returned. However, if no data is returned, the subreport doesn't show at all. I have a message on the subreport I would like to show if no data is returned, but the subreport won't show at all. Any ideas??
I have constructed my main report using the table item from the toolbox. In maintable I have two groupings.
In the maintable I have grouping2 set for a page break after each grouping.
I have set the subreport parameter to the field value of the maintable grouping2.
I want to display the contents of the subreport after grouping1 of the maintable on the same page.
I have achieved a result by placing the subreport in maintable_group2 footer. However when I save it as an excel sheet it says "Subreports within table/matrix cells are ignored".
I really don't like how the report displays like this in the report server however I will need to export this report to excel.
I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at. There is around 10 detail records per 1 main record. and the main table is around 7 mil. records
Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.
OR
I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. ) :confused: Suggestions
(My attitude is going towards the trigger way but 100 percent sure)
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
I need to track all changes to my database at the application level. Say if anybody logs into the application with his user id and password, any add,delete,update should be tracked. This is an application with 5000 transactions a day. I thought of using a trigger on all the tables to capture the following fields.
[UserID]
[AuditDate]
[TableName]
[PrimaryKey]
[Action]
,PreviousValue]
[CurrentValue]
I thought of adding all the application user as a database user. So then userID will be SYSTEM_USER , and AuditDate will be GETDATE(). Is this a good idea?
I dono whether the field primarykey is really essential in my audit table? Please advice.
The field 'Action' will be update/delete/insert. Previous and Current value will be the entire record from the temp tables inserted and deleted in triggers. I dono whether this is a good idea to store the entire record or just the field which was changed or updated(as updates r gonna be more than insert and delete), and the entire record for insert and delete. Is that possible? We have columns_updated() in sql server 2005 where we can we find the columns which was updated. Will that work?
Can i capture all actions(inser/update/delete) in a trigger with FOR INSERT,UPDATE,DELETE ? If so, how can i update "ACTION" and implement something different for update(as discussed earlier) . Is there any other field which you think i should capture which might be important as a part of auditing or tracking.
Can we use trigger for all tables in the database at one time?
I know there are too many questions . I wud greatly appreciate any thoughts or help for any portion of it.
I need to be able to audit the command Insert - Update - Delete sent to a table. I have attempted with a trigger but I have not been able to obtain the fields that have been upgraded.
It is possible to generate in the trigger the command Update - Insert - Delete sent?
Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry Here is my trigger:Create TRIGGER tg_audit_task_order_awardees on xxxfor INSERT,UPDATE,DELETE as INSERT INTO audit_task_order_awardees( audit_log_type, to_awardee, solicitation_id, contract_id, order_number, amount, show_public, audit_changedatetime, audit_user) Select 'OLD', del.to_awardee, del.solicitation_id, del.contract_id, del.order_number, del.amount, del.show_public, getdate(), del.modified_user FROM deleted del
/* for a new record */ INSERT INTO audit_task_order_awardees( audit_log_type, to_awardee, solicitation_id, contract_id, order_number, amount, show_public, audit_changedatetime, audit_user) Select 'NEW', ins.to_awardee, ins.solicitation_id, ins.contract_id, ins.order_number, ins.amount, ins.show_public, getdate(), ins.modified_user FROM inserted ins
I'm trying to create and Audit report for different tables.I'd like to use an generic table structure for the report which will be temp.I was thinking something like this:
I've already created the audit table with same structure plus all field duplicated and action type. (this was a request)Now I need to group it for the report.following an example of audit table I've created
tblTest_audit ( ID ID_new Name Name_new Address Address_new )
We would like to create a audit table for storing information if anyobjects like tables / stored procedures are dropped.Table would also contain information about time and the user name whohas dropped the object.Any help would be great.Thanks in advance.Kamal
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
Does anyone know of a quick way to audit all users in a database and display their rights and permissions on a table level. I would hate to have to do it one user at a time. There has to be an easier way.
I'm going through a Sarbanes Oxley audit and need to provide them this information.
1. It looks like a 'ª' delimiters between fields. I need to pull out the second column in the above example it is - 9999999999. This is the place reference, so I need that in a separate field. 2. I need a way to compare the two fields and report back the change. So in the above example shows EAST. The After image shows WEST - I need the before image of EAST in one column and the WEST in another column.
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance] ON [dbo].[tbl_attendance] AFTER DELETE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
Currently running Sql Server 2005Is it possible to issue the delete command and capture the affected rows asxml types that will be stored in an audit table with an xml column?Something along the lines of:delete from source_tableoutput(deleted.*into audit_table (xml_audit_column)for xml auto)where source_table.column = @delete_value
I am looking to track any changes made to any table within a db into a single audit table which will hold as fields: the table that has been updated/inserted, the field that was changed, its primary key, the old value and the new value specific to that field, and the date it was updated/inserted.
From what I have read, it does not look like this is possible with a trigger on table as it is not row specific and that I might have to control this from the business layer (vb.net). I am correct in this assumption, or is there a way of tracking specific data changes through triggers.
