Keep Track Of When And By Who A Record Was Created And Also When And By Who The Record Was Last Updated
May 26, 2008
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID()
RETURN Date(Today)+Time(Now)+USERNAME
END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
View 10 Replies
ADVERTISEMENT
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
Mar 14, 2014
I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.
i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.
how to do that?
View 5 Replies
View Related
Jul 6, 2007
Hi -
apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit.
I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried:
1 UPDATE Timeslot
2 SET StartTime = @StartTime, EndTime = @EndTime
3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND
4 (EndTime < @EndTime);
5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row?
Thanks!!!
Oliver
View 12 Replies
View Related
Dec 11, 2006
I have some set of records in my table.
The same set of records will be updated often. Now I have a column as "lastupdated"
While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed.
Means, the recently updated records should be displayed in datagrid.
Pls give me the sql code / i am also in need of a Stored procedure for this.
I am working in SQL 2005
View 2 Replies
View Related
Jun 9, 2005
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
View 1 Replies
View Related
Nov 15, 2001
Does anyone know how to get the indentity of the last updated record in a table. I need it for a cascading trigger.
Thank you,
Jody
View 3 Replies
View Related
May 21, 2006
I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message.
My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far.
I am new with Triggers, so any help or advice is appreciated.
Code:
CREATE TRIGGER trgPaperCheck
ON BOOK
AFTER UPDATE
AS
IF UPDATE(Paperback)
BEGIN
DECLARE @count AS INT
SELECT @count = COUNT(Paperback)
FROM Book
IF @count > 1
BEGIN
PRINT('Only one Book record can be updated at a time!')
ROLLBACK TRANSACTION
END
ELSE IF @count =1
BEGIN
PRINT('Paperback Updated')
COMMIT TRANSACTION
END
END
View 2 Replies
View Related
Mar 10, 2008
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
View 1 Replies
View Related
Nov 22, 2007
Hi
I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation.
On the event handler I am usin select ID,Name from my [table];
this will return all record from database.
I just want to get the record which is inserted or updated.
Can u help me in that.
Take care
Bye
View 4 Replies
View Related
Feb 21, 2006
I have a table that contains a field LastUpdated. This field should contains the date of the last update of the record.
Is there a way to auto-update this field value to the getdate() value each time the record is "touched" by an UPDATE sql command?
Thanks in advance,
Joannes
View 1 Replies
View Related
Mar 30, 2008
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead.
select Lead,LastUpdated from dbo.KPITbl
I have a table with data that looks like this:
Lead LastUpdated
----------- -----------------------
JOHN SMITH 2008-03-26 08:45:00
JOHN SMITH 2008-03-20 09:33:00
MEG RYAN 2008-02-21 16:16:00
JOHN SMITH 2008-02-21 16:19:00
MEG RYAN 2008-02-21 16:22:00
JOHN SMITH 2008-03-28 16:10:00
JOHN SMITH 2008-03-28 08:49:00
JOHN SMITH 2008-03-23 19:23:00
MARK MCRAE 2008-03-27 03:12:00
MARK MCRAE 2008-03-26 08:48:00
MARK MCRAE 2008-03-26 08:46:00
JOHN SMITH 2008-03-26 08:47:00
JOHN SMITH 2008-03-26 08:48:00
ALLAN WHITE 2008-03-26 08:43:00
ALLAN WHITE 2008-03-26 08:40:00
JOHN SMITH 2008-03-26 08:48:00
Thank you appreciate it.
View 2 Replies
View Related
Mar 10, 2008
Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
View 1 Replies
View Related
Apr 24, 2015
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
View 1 Replies
View Related
May 8, 2008
Im using a trigger to check updates on particular table and execute a email. it works but it doesnt show the right record
im looking into one table called SiteInfo.
here is my code
Im using sql 2005, can someone look at my code or the select statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER TTSUpdate
ON SiteInfo
FOR UPDATE
AS
declare @SiteID varchar(10)
declare @Body2 varchar(2000)
declare @Sitename varchar(50)
declare @TTSCreate varchar(30)
declare @TTSCreator varchar(50)
declare @Subject2 varchar (100)
SELECT @SiteID = SiteID,@Sitename = AccountName,@TTSCreator = TTSOwner,@TTSCreate = TTSCreatedDate
from SiteInfo
SET @Body2 = 'New TTS site created: ' + @Sitename + ' With TTS Site ID:' + @SiteID + ' TTS was created on: ' + @TTSCreate + ' By:' + @TTSCreator
SET @subject2 = 'New TTS site created: ' + @Sitename
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TTSAdmin',
@recipients = 'email address here',
@subject = @subject2,
@body = @body2
GO
View 3 Replies
View Related
Mar 19, 2008
I have a table with several datetime fields but they all list just the correct date. The time is always 12:00:00 AM. I don't have a field that records the actual time a record was created. I have a timestamp column, but it appears to get updated everytime the DB backs up. Is there a way using the date fields I have or otherwise, to determine the time a record was created?
View 1 Replies
View Related
Oct 23, 2007
Hi All In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.Thank uBaba
View 4 Replies
View Related
Jun 15, 2004
I've gotten conflicting info about this in the past so I thought I'd try to get clarification.
When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?
Thanks
View 3 Replies
View Related
Apr 16, 2014
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
View 4 Replies
View Related
Jul 15, 2015
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
View 2 Replies
View Related
Jun 18, 2007
Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
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
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 6, 2007
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
View 5 Replies
View Related
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Feb 26, 2008
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
View 3 Replies
View Related
Jan 22, 2008
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
Best Regards,
Hans
View 8 Replies
View Related
Mar 1, 2006
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
View 6 Replies
View Related
Apr 17, 2014
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
View 2 Replies
View Related
Jul 20, 2005
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies
View Related
Jan 14, 2008
Hi Everyone-
i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on
can anyone help?
thanx
Maylo
View 8 Replies
View Related
Apr 23, 2008
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
View 4 Replies
View Related
