Hi, i am trying to find a trigger that is updating a field in one of my tables.
I have a field called [ProfileDate], whenever a member is profiled, this field is populated with a GETDATE() value. I am trying to track down the trigger that updates this field.
Any help will be greatly appreciated
Kind Regards
Carel Greaves
I'm wondering if there's a good way to sort of pass a value to a trigger, in cases where it needs to store more than just what it can collect from the table it is triggering off of.
We have a database that is used by several applications. Each application connects to the database using a distinct SQL Server Login. So, "Application1" might connect to the database using SQL Server Login "Application1Login".
Each application can be used by any number of users who "log in" to the application. This process merely authenticates them against a stored username and password in the database, but it uses the "Application1Login" for actually connecting to the database. So, when users log in to the application, they're all using the same SQL Server Login to connect to the database. This isn't something that is likely to change, though I realize my question could be answered more simply if each person using the system connected via a specific SQL Server Login.
Whenever an action is performed against the database by an application user, we pass the UserId of the user. We store that UserId in tables when they are inserted or updated. We also have History Tables on several tables, with triggers on Insert, Update, and Delete.
The Insert and Update triggers work great: The stored procedure sets the "LastModifiedByUserId" field in the table to the passed UserId, which causes the history table's entry to use that newly changed information. So, we can always tell who inserted or modified a History table entry by setting the Last Modified By field in the Insert or Update.
The problem is with deletions. Since deletions don't set the LastModifiedByUserId field (it doesn't set fields; it's deleting the row), then the trigger has nothing new to go on. The trigger will cause the History table entry to use the last known value of the LastModifiedByUserId field, which is probably not going to be the person who actually deleted the record.
My solution to this problem is to create a "HistoryDeletedUser" table which only contains a single row, and a single column, which is used to store a UserId value. When performing a deletion, I would first update that table, setting the UserId to the current user's UserId. Then, I delete from the appropriate table. The DELETE trigger would then select the current value from that table, and insert it into the History record as the UserId of the person who deleted the record. After the deletion, I would set the HistoryDeletedUser to Null. As long as I perform my queries within a transaction, it should always be accurate (though I suppose that queries that would otherwise be unrelated to each other would now have to compete for Lock access on the HistoryDeletedUser table...)
Please Help.
How do you track changes to objects in SQL Server.
For example changes in stored procedure,views and indexes. What system table or column track or indicate changes in text of sp or views.
Help appreciated.
Regards
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
View 2 Replies View RelatedHi,
I am trying to create a tracking spreadsheet for my SQL Servers in order to keep track of every thing.
Here is a example of what I have:
DateTracking IDServerIssuesSolution
8/26/20046760FORMSCAPEDOCSBackup failSwitch Transaction logs to the D: and left the database backup on the E:
Should I be more detailed and add some more fields and if yes can you recommend some.
Thanks
I have an application that uses SQL server. Is there a way to find out what SQL statements the application is passing to the SQL server?
I would like to know all kinds of activities going on in the background of the application. I am sure there is a way.
Thanks in advance.
OK. For DDL, please refer to the classical Northwind ORDERS table,problem/challenge, find the longest duration (start_date andend_date), during which, no orders were placed.FYI, column names by the order of colid per syscolumns:OrderIDCustomerIDEmployeeIDOrderDateRequiredDateShippedDateShipViaFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountryAny idea/approach? TIA.
View 1 Replies View RelatedHi
I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.
any ideas ?
thanks
Yesterday I had problem with that a sp that contained join that the developer had forgotten the where part. The sp join very large tables and sp took more 95% dual itanium processor. How do fastest track this down when it accrues. Find the sp or sql that running and consume this much processor resources. I tried with a lot. Sp_who, sp_who2, activity monitor.
View 5 Replies View RelatedI'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?
View 3 Replies View Relatedim trying to create a procedure that will insert/update a small table
that has an ID, AmtBefore & AmtAfter.
this is just a table that i'd be using to monitor other activity on the database.
i'm really pulling a price + ID every hour and i need to track that somehow and know when the procedure runs if a price is lower than it was the last hour, plus of course keep the ID's straight.
does anyone have a good approach to something like this?
thanks for any help on this
rik
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
View 4 Replies View RelatedHi all,
I have several transaction tables on which I need to track the changes. That is I need to maitain
the history of changes. Only few column values are changed often.
Which is the best way for tracking the changes.
1.Store the whole record after the change ?
Or
2.Store the ColumnName & its respective old & new value ?
Or any other better.
Note : UI part & SP's will take care of the tracking & no plans for triggers.
Thanks in advance,
HHA
Hi all how do I track when a users opens, enters data in a database. How can I track them??
View 1 Replies View RelatedI have a table in my database and it holds some important information,I want to track the users
who are executing DML commands(select,update,delete) on the data in that table.
Is there any way do that?If so how could i implement that.can any one help me in this regard?
Thanks in advance...
Hey everybody,
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
Thanks in advanced guys!
Hello everyone, I have a fairly unique need :) I am trying todetermine the use/clients for databases in my corporation that I ammaintaining, but that noone seems to know what they are for. Many ofthese databases never seem to have anybody connected to them in thecurrent activity.What I'd like to do is find a way to audit the logins, so everytimesomeone connects to a database it simply logs the clients IP address,what login they used, and maybe what time. I've been searching googlefor this and have found tons of information on auditing the logins,but not the clients, such as by ip. Any help in this regard would beGREATLY appriciated!Joshua
View 2 Replies View RelatedI have a device application that simply needs to upload data to a server. The preferred DB server is Oracle but I've made it work using RDA and SQL Server. The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method. The data arrives just fine, the first time. With every subsequent upload all of the previous data is deleted fromt he server. Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server.
My question is: Is it possible to prevent RDA from deleting data on SQL Server? I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.
I hoipe someone can point me in the right direction here.
I have an application with the following requirements (using SQL CE 2 alas)
A set of tables on the server that need to be imported to the handheld. Using rda, I need to get the modifications to these tables from the server (add/edit/delete) but the handheld will never update these tables.
A set of tables on the server that need to be imported to the handheld. The handheld needs to add/edit existing records, and it needs to get any changes from the server.
A set of tables on the server where the handheld needs to import a subset of the records. It needs to add (but not edit) new records, upload the new records to the server, and download any changed (add/edit/delete) records to the handheld.
What tracking options should I use in these 3 cases?
The problem comes in that I need to have some foreign key relationships in the database on the handheld. Since rda munges the names of primary keys (and indexes), I do not know of a good way to add these foreign key constraints. Any suggestions?
Thanks
Hi,
I want to keep track of data change and want to enter the pk such as testkey and timing in different table. is it possible without having to write trigger on the table!!!
I've got a table that has frequent updates to it. I want 100% change tracking on this table though, so we can rollback to any previous version, or just see any changes people make.
Is there a best practice for things like this? Currently, I'm using a trigger on UPDATE to take the previous values and store them in a history table. This keeps track of who changes what, and when. Plus the most recent data is seperate and more performant to access.
I've also heard about putting an 'IsActive' flag on the main table and any changes that are made just get marked as In-Active and a new record gets added.
Any input?
Thanks!
Hi,
I have a bit of a problem I could do with some help with please...
I am updating an old windows client server app that uses ADO 2.8. Slowly but surely I am migrating the code from VB6 to .NET and this now means the ADO code is going to be replaced with ADO.NET.
As it is a client server the application used to establish an ADO connection and hold on to the connection for the entire duration of the app being alive. When a user signed in it would record the user logon code in a database table along with the current @@SPID value.
When the user does something in the application it typically usees an SP to do the database work. Inside the SP it also audits the work being carried out, of course the audit trail records the user id that did the work. The way it determines who called this SP is to query the logon table using the current @@SPID to get the users logon code (remember this was paired up when the user signed in).
This all worked very nicely as only one connection was ever being used and it was always the same connection that was used. However, when I come to use ADO.NET this is no longer valid. I want to use the connection pool and follow the best practices where my connections are always obtained as late as possible and released as soon as possible.
This leave me with the problem of how to get the current users logon code in to the SPs in order for them to continue writing the audit trail. Currently I am thinking that I am going to have to send the users code in to the SP as a parameter, I just wondered if there was an alternative.
Any help would be great.
Regards
Graham
Hi,
Does any one have any idea of how to track a user access to a spesfic DB because we have an old DB and we don't know if any body using it, I know Profiler can help but I need to know when it was the last time this DB has been accessed.
Thanks
Hi,
I have to monitor a stored procedure.
I would like to see how many time this sp is been called from the application and what was the response time?
And then I'd like to saggregate the results - say in 0-1 second x number of hits occured and then from 1-3secs y number of hits occured?
Any better way of doing this then to run a trace?I am not ina mood to run a trace cuz it's a prod.box.
TIA.
pd
Hi,
Full scans are bad, and I know this. I am seeing some on my database, and I know that some are unavoidable....but what I would like to do is find out which queries are causing them. I have over 500 users in my DB at any one time, and SQL Profiler doesn't seem to help me.....it shows me what objects are being scanned, but not if the scan is a full-scan or an index scan.
Does anyone have a way to find out what is causing my full scans?
Here is the situation. I have an employee who is making changes but I can't prove it. He thinks he knows more than he does and he's mess'n everything up. I would like to know if SQL or some third party product has the capability to see the change and log what was changed with the persons username attached?
View 1 Replies View RelatedTrying to determine a way to see if stored procedures are being used. I am not sure of a way to see when and if a stored procedure is being called although I have thought of using sp_recompile to set the stored procedure to recompile but I am not sure in which table that flag is set so I can see which ones were not recompiled. Any assistance or guidance would be appreciated. Thanks.
View 3 Replies View RelatedHi everyone.
I am trying to find a way to calculate everyday my DB Growth, I did find a script on some site but it seems to give me the same information as the taskpad wich is not very specific. Basicaly i would like to know the size of a table in MB or in whatevever conversion possible, so that i will be able to do some forcasting.
Any help here would be greatly apreciated.
Hi there,
Is there a way in SQL Server to track changes that were made to a stored procedure. See when it was modified and what changes were made?
I'm trying to chase the history of one stored procedure that was changed and affected a live system from running correctly.
Thanks in advance!!!!
S
so i have an update query that is updating 50k rows per batch. my question is how do i campture how long it is taking to update 50k rows per batch.
View 3 Replies View RelatedI am looking for an efficient way to track all modications to a a database. I have come up with a few ideas but none are very efficient.
View 6 Replies View RelatedI have enabled CDC on my productive database with
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GOand I have enables cdc on tables within the database using
USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
GO
As per [URL] ....
Data changes are then stored in a system table called cdc.dbo_MyTable_CT So far so good.I backup the database and restore it
backup database MyDB
to Disk = 'CBackupMyDB.bak'restore database MyDB1
from disk = 'CBackupMyDB.bak' No table called cdc.dbo_MyTable_CT in system tables!
Have I missed a step? and is there a way to get these tables into the backup.I am now building a ssis job to move the contents of the cdc.dbo_MyTable_CT to an archive table, but i am worried that if I had to restore the database to a point in time I may not be able to do it for these tables.
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Experts Only Please. (jk)