Log Trigger - Keeping Track Of Status Changes
Aug 28, 2015
I have a trigger that keeps track of status changes...
IF UPDATE(STATUS)
BEGIN
DECLARE @currentdate datetime
DECLARE @currentstatus integer
DECLARE @UserID integer
DECLARE @PermitID integer
DECLARE @Status integer
[Code] .....
It works but not the way I want it to. The @currentstatus and @newstatus are the same. I want the status before and after the update. I asked around as to how to do this and some one told me to use the Deleted table.
View 3 Replies
ADVERTISEMENT
Nov 7, 2001
Hi
We need to keep track of all changes that are made to our tables.
The changes will be saved in a table that records:
- the table in which the change was made
- the name of the field that was changed
- the old data for the field
- the new data for the field etc..
I've seen a few examples that record the name of the table that was
modified but none that record done to the field level.
Can anybody give some guidance?
Thanks..
Wayne
View 1 Replies
View Related
Nov 13, 2007
Hello All,
I have a problem concerning keeping track of a value within a query.
I have a table that tracks invoices recieved and payments made.
For each invoice number there may be multiple payments made against it.
I need something that will check and make sure that each invoice number has its payments equal to its received amount.
Any help would be greatly appreciated.
Thanks,
View 5 Replies
View Related
May 8, 2007
Let's say I develop a 'version 1' of a program/database and ship it to some customers.
Then I continue work on version 2 with some databasechanges etc.
When version 2 is shipped to the customers, they need a way to upgrade their database
from version 1 to version 2. I guess this is easiest done with one/several sql-scripts.
What I want is your opinion about the best way to keep track of the changes between
version 1 and 2. Here are two ways with pros and cons I've thought of:
1 - Keep manual track of every change (index, changed columns, relations etc) and update a scriptfile with all changes.
Pros: Full control of what is happening
Cons: Pretty much extra work and risk of missing some changes.
2 - When version 2 is ready, run some third party tool to get a diff between the databases
Pros: Fast and easy
Cons: The DB-changes may need to be applied in a special order, with default values, etc etc and I'm not sure all tools handle this in a good way...?
So... what is your recomendations? I'm sure this must be a headace in every development project?
Regards Andreas
View 13 Replies
View Related
Jul 5, 2006
I'm actually taking Microsoft's 2779 and just finished a lab where wekept track of our changes to the database.However, I'm not happy with the scripts interface because it does nottell me the chronological order of my changes to the database.Could someone share with me their technique for keeping track ofdatabase changes?I'm actually thinking a set of tables would be best, because sometimesyou want to know what database object you made a change to and othertimes you want to know when you did something...
View 2 Replies
View Related
Mar 10, 2014
I have four fields in two different tables.
ID, field1, field2 and field3 in Table 1 has value 1 or 0. Type integer.
ID, field1, field2 and field3 in table2 are text fields.
If field1 in table1 has value 1, then there should be a text value in corresponding field in table2. That is, in field1 in table2.
However, if there is value 0 in field1 in table1 there should not be any value in the corresponding field in table2.
Now it is not always so, which do that I want to make a track of which fields miss match in these two tables.
How do I list them on the smartest ways? ID links the tables.
View 3 Replies
View Related
Jul 3, 2014
I have a requirement. I am having a database which is having views,procedures and tables. Many users are having access to the database. If i want to track all the schema changes happening in the database how to achieve that? As in i have used some tables in a procedure and someone might change the column or drop the column. So it is going to have impacts on my procedure. I need a trigger which tracks all the changes done in a database and the impacts those changes are going to make.
View 2 Replies
View Related
Sep 6, 2007
My goal is to create a trigger to automatically set the value for a status id on a table based on set criteria. Depending on the values of other fields will determine the value that the status id field is assigned. So far, I have written the trigger to update the status id field and I go through a seperate Update statement for each status id value. The problem is that I can't get this to work at the record level. The problem that I am getting is that if I have 50 records in TABLE1 and at least one of them satisfies the where clause of the update statement, all of the records get updated. So, using these two update statements, all of my records end up with a status value of '24' because that was the last update statement run in the trigger. Here is the code I have so far:
CREATE TRIGGER dbo.JulieTrigger1
ON dbo.Table1
AFTER INSERT,UPDATE
AS
BEGIN
BEGIN TRY
/*Update Table1.Status to POTENTIAL (id 23) status */
UPDATE TABLE1
SET status_id = 23
WHERE EXISTS (SELECT *
FROM TABLE1 a INNER JOIN TABLE2 b
ON b.order_id = a.order_id
WHERE a.start_dt IS NULL
AND b.current_status_ind = 1
AND b.lead_status_id NOT IN (15,16)
AND a.order_id = TABLE1.order_id)
/*Update Table1.Status to ACTIVE (id 24) status */
UPDATE TABLE1
SET status_id = 24
WHERE EXISTS (SELECT *
FROM TABLE1 a
WHERE fill_ind = 1
AND (end_dt IS NULL OR end_dt > getdate() )
AND a.job_order_id = TABLE1.job_order_id)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN
-- Return the error to the calling object
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
END CATCH
SET NOCOUNT ON;
END
GO
Thanks in advance for any help!
-Julie
View 1 Replies
View Related
Apr 25, 2000
hi, Is there a way to view any Transact sql in a log for instance, I am iserting a new records into a table. how can I track a log for the isert statment. I know that using bcp code provides me with a log if I use /o in the bcp code. Is there any similar thing for Transact sql
thanks
Ali
View 2 Replies
View Related
Jul 20, 2005
hello,I was wondering how to track all changes on tables by using some sort of ahistory table.What i would like is a generic history table where i can see who updated,inserted, deleted or executed(stored procedures, triggers) what value inwhat table with a date when it was occured.Could somebody help me with this?
View 1 Replies
View Related
Aug 26, 2002
SQl7, sp3, NT4
How do I keep th job history of a job, say if I re-create the job?
We recreate the jobs often as part of a code move, but I'd like to retain the history of the previous jobs?
** sp_help_jobhistory -- only shows the jobs that exist, and not old jobs that no longer reside on the server.
Thanks,
AJ
View 1 Replies
View Related
Jan 10, 2005
Hi all, here are my goals: Have the same DB on two different stand-alone computers, and keep them up-to-date from each other.
Basically a user would input to a DB for a week. Then every week or two, update the other stand alone DB with the new input. The DB would be exactly the same.
What are my options for this? I'd like it as easy as possible! Are there any software packages that deal with this type of transfer, etc.? Thank you!
View 5 Replies
View Related
Sep 24, 2006
Hello
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helping
View 1 Replies
View Related
May 23, 2008
Hi, I have the following code in a query:
SELECT [Issue date],DATEDIFF("dd",[Issue date],[Start date])/365 AS runningdays
FROM Database1..[Insurance Policies Working DB]
WHERE [Policy Number] LIKE '%1368529%'
The part 'DATEDIFF("dd",[Issue date],[Start date])' comes out as 364 if calculated on its own. However, then when it is divided by 365 it comes out as 0. How do I get it to show as a decimal instead of just rounding it down automatically? (Hope I've made sense)
Thanks in advance
View 3 Replies
View Related
Jul 1, 2003
SQL Server 7.0
A stored procedure has been executed either yesterday or today. I would like to find out which user and when they executed it. Is this possible without 3rd party transaction log interrogation tools?
Thanks,
Nev.
View 1 Replies
View Related
Jul 23, 2005
What would be the best practice to follow to keep track of MS SQLserver changes... Stroed procs, tables, views, triggers, indexes, DTSand also jobs ect....I am not quite sure how Source safe works with sql server. Any otherway to do this... Even if its manual work, its okey.. I wouldappreciate if any of the DBA's let me know how they are facing thisissue....Thanks in advance...
View 4 Replies
View Related
Dec 18, 2007
Situation:
2 servers: 1 production, 1 test.
While my application is running on the production server, I want to develop on the test server. After a few weeks, I want to update the application, and have to update the database structure on the production server also with the most recent one from the testserver, but without deleting the current data on the production server.
I create/modify all tables in SQL Server 2005 via Management Studio. The application is built in VS2008 Pro. I'm using SubVersion (SVN).
I can let Management Studio generate scripts for every change and store them manualy, but that's a little bit too much work. What I want is a sort off version control solution. A solution that a service running on the background tracks every change on a specific database on my test server, and stores them as T-SQL code in a repository. Then I can collect all those T-SQL scripts, and run them against the production server.
Is there some solution for that?
View 2 Replies
View Related
Nov 28, 2003
we have a simple table
Key, Name, Address, City, State, Zip ................ect
I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.
I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.
Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.
Appleby
Jones
Robertson
This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.
thanks in advance -arthur
View 3 Replies
View Related
Feb 5, 2004
I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?
View 4 Replies
View Related
Feb 27, 2007
hi, is there any possiblity to keep break point and run the step by step compilation as like we do in .net,if means please telll me how to do?
View 3 Replies
View Related
Oct 16, 2006
Hello everyone,
I have a winform application with C# front end and sql express 05 backend.
In this database i have a table that holds manufacturer provided pricing and the manufacturers we work with update pricing constantly.
We have one table called "manufacturerpricing" which we are constantly inserting and deleting pricing records to/from to keep manufacturer pricing up to date. We may insert and delete as many as 2,000,000 records per month into this table.
This works perfectly fine and we have no problems here at all.
But with that being said, I am worried about the size of the database growing out of control due to temporary space etc. The database just keeps getting bigger and bigger.
How do I run some maintenance to keep the database size under control.
I would like to run this automatically from the C# front end so if ther is a stored proc I can call or an C# assembly I can reference that would be ideal.
Any help is greatly appreciated.
View 3 Replies
View Related
Dec 14, 2006
Hi,
I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.
Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?
Thanks,
Alex
View 4 Replies
View Related
Jan 24, 2008
This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.
Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?
View 4 Replies
View Related
Aug 23, 2006
I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose.
History
guid (uniqueidentifier)
dateCreated(datetime)
lastDateUpdated(datetime)
changesMade(varchar(50)) ---- eg; dropdownlist
oldValue(varchar(50)) ----- compaq
newValue(varchar(50)) -----dell
updateBy(varchar(10)) <------ username of registered user
comNo(int) = foreign key for Computer table
History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.
View 17 Replies
View Related
Oct 6, 2005
I have an application that has a SQL back end, and I want to be able to track who is accessing a record so that no one else can access it at the same time. I was going to do this with a table or application state, but how can I avoid keeping files locked when someone abandons a session? Any Ideas?
View 1 Replies
View Related
Nov 19, 2005
Hi
I have a stored procedure for which i need to track the changes.
I wanted to know which user has updated the stored procedure and what all changes are done to it.
I thought of getting these details from the transaction log, but looks like sql server 2000 doesnt have any log reader.
Kindly help
Thanks in advance
Sreenath
View 2 Replies
View Related
May 20, 2002
Hello,
I have a table which consists of 27,000 of records. Among these records, there is one record which
is a duplication of another record.
Is there any way to track this record from the same table by the SQL statement ?
I have been advised to use the following statement but it does not help:
Select count(*) As Duplicate, columnname from tablename group by columnname
Scrolling 27,000 lines of records with bare eyes is very painful.
Any help is appreciated.
Cheers
View 2 Replies
View Related
Apr 12, 2005
Hi all,
I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday.
thanks in advance,
Rohit
View 1 Replies
View Related
Nov 8, 2004
I need to be able to track which users and the number of times a user connects to a particular SQL Server database. I have a simple SQL Server database that users query (SELECT only) using an Access 2000 front-end. I do not have any control over the front-end so my only tools on the database side. I can't use 3rd party software, just SQL Server. Is there any way? Thanks.
View 1 Replies
View Related
Sep 2, 2014
In my Sql Server 2012 I have the following table in a database
CREATE TABLE [dbo].[HumanResource](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[IsHired] [bit] NULL,
[Address] [nvarchar](max) NULL,
[Score] [bigint] NULL,
[Code] ....
Considering that sql server Update is a chained Delete and Insert, the first row is the delete row capture and the second one is the insert row capture.
But I don't understand why the address is tracked as NULL when I update other fields.
View 1 Replies
View Related
May 11, 2007
I have a strong feeling that this isn't possible but I thought I might as well ask...
I'm developing a database application (SQL Server 2000 backend) where the client and the server is separated over a slow network connection (satellite). There are parts in the application where I will have to query a large resultset so I was wondering if there is a way to determine the percent complete of a query so I can put a progress bar on the interface so the user can see it loading data instead of having a frozen form.
I thought about spliting up the query into different ones and update the bar once each separate one is complete but I'd rather not do that because in the application development environment I'm working in, I have to close the resultset and reopen it every time I do a query... unless this isn't a big deal but I'm under the impression its something to avoid.
Thanks!
View 4 Replies
View Related
Aug 29, 2007
different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists)
I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements...
1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?)
2.If somebody know where we can take good parser which will help with this job?
Thanks
View 14 Replies
View Related
Sep 20, 2007
I am an advanced user of MS Access and other databases but relatively new to SQL Server. I am utilizing SQL Server Express to analyze a database that was created by another entity. I am interested in looking at any specific record in any table and seeing who created that record, what date/time the record was created, who edited the record, when that edit occurred, etc. I don't see any such fields in the database. Would the designer of the database have to have explicitly added such functionality, or is this information available but just hidden from the novice's view?
Thanks for your help.
View 1 Replies
View Related