Can &"Date Modified&" Col Be Automatically Updated W/o Trigger For Each Table?
Hello,
I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated?
Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables.
Thanks!
View Complete Forum Thread with Replies
Related Forum Messages:
Finding Last Updated/modified Date
Hi, I am using SQL Server 2000 and have the following questions: 1. How do I know the last updated (data) date using system objects or any other method? 2. How do I know the last modified date of a table using system objects or any other method? 3. How do I know when a table is last accessed Any help is appreciated Thanks for your time and help in advance Kumar
View Replies !
Updating Modified/updated Date Column?
If your tables contain created and modified/updated dates what is the best practice for these? 1. Should you use UTC dates? 2. Do you use a default for the creation date (I assume yes)? 3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data? Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.
View Replies !
How Is Represented The Updated Table In A Trigger?
Hello I am a newbie creating triggers and I would like to know what is the name of the updated logical table. That is, since the are identifiers like "inserted" and "deleted" that represent the inserted and deleted rows, respectivaly, I presumed that "updated" existed - but it does not. I have a table with a column named "UpdatedTimeStamp" which I would like to update anytime the other columns are modified. I intended to create the following trigger: create trigger myTrigger on myTable after update as update updated set UpdateTimeStamp=GetDate() Of course, "updated" is not a valid identifier that represents the updated row. Please, help me in creating this trigger. What is the correct way of doing a trigger like this? Thanks a lot in advanced.
View Replies !
How To Create A Trigger Which Automatically Set The ID Of My Table
hi, i have 2 tables where i require an unique ID over both. I googled a bit and discovered that i need to create an own table for this which holds the last value (because mssql unfortunately does not support sequences). i did this but my problem now is that i want to automatically set the id of my tables with a trigger on INSERT. hope this is possible .. or anyone have other suggestions? thanks a lot!
View Replies !
Table , Column Created, Modified Date In Sql Server 2000 Or 2005
Hi Any one please tell me is there any possible way to identify the table modified date. I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates. Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000. I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date can any one help
View Replies !
Automatically Trigger A Sum From One Table To Another Upon Update/insert Query
I'm trying to update (increment) Company.SumtotalLogons from CompanyUsers.NumberOfLogons where CompanyUsers.CompanyID = Company.CompanyID I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons I know this is possible in access, so i'm wondering how to go about it in ms-sql? any ideas?
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
Modified Date
I am taking a "complete backup" of my production db every day using Backupagent of Arcserver 2000. But the Modified Date of .mdf and .ldf files show an older date. Is it normal? Thanks Wilson
View Replies !
Modified Date
Is there a way to see the last modified date on the Table structure or a stored Procedure in SQL Server 2000? Thx
View Replies !
Get Modified Date Of File
Hi, the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database. Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error. Any thoughts how to do this please? Thanks
View Replies !
File And Date Modified
Hello all, I was just thinking about a situation which I have been tasked with in that I have a package that is scheduled to run once a day which will import data from a flat file. The data does not have any distinguishing time that can be used to see whether or not it is beyond a last imported time. I was wondering, is there a way to access the date modified of the file? If so I could simply append this time to a table which keeps track of all of the files and the modified dates which I have ever imported for this task.
View Replies !
Get File Modified Date
Hi, the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database. Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error. Any thoughts how to do this please? Thanks
View Replies !
Win Date Modified Field On MDF Files
I'm an Oracle DBA and just getting used to MS Sqlserver. I noticed that the windows explorer "date modified" field for my database files ( .MDF files ) doesn't change much even though there is activity going on. Sometimes it doesn't change for a week. Is this the expected behavior? Could it be that no data is changing in my database? ( I find that hard to believe) Thanks for any insights.
View Replies !
File - Date Modified Attribute
I am looking to create an SSIS package to import text files into an SQL table. I'd like to import the 'date modified' attribute from the text file into one of the columns as well as the data within the text file. I'm thinking an ActiveX task and variable currently, just wondered if anyone had any other thoughts on how this could be achieved. Thanks
View Replies !
Help With Trigger: Updated Oct 11, 2005
Nevermind, I'll just use a stored procedure. Update: I've figured out what is going wrong now. I'm trying to find the subaccounts from the old invoice using the new invoicedate. This is doomed to fail! I've worked out a way to save the old invoice date in the C# programming, but is there anyway to make that value accessible to the trigger? Note that there will be nothing useful in the deleted table--this trigger is on inserts only. Any help is appreciated!
View Replies !
Sql Database File Last Modified Date And Attribute Question
I have a database that is actively being used and updated. When I look at the last modified date of the mdf file, it shows as a couple of weeks ago date and the transaction log file .ldf file shows a couple of days ago. wonder if the database is constantly updated and changed, should it show the currently date instead. Also, the attribte of mdf file show A as archive. what does it mean? Thanks
View Replies !
Any Column Updated / Inserted Trigger
Hi,I'm a SQL Server newbie, so I'd appreciate if someone would tell me ifthis is possible. I'm running SQL Server 2000 on Win2k ServerI have one table with a large number of columns. I have two pieces oflogic that I'd like to execute depending upon whether an insert or anupdate statement was executed on that table. I'd prefer thisexecution to occur from within a single trigger. If a row isinserted, then I would like to execute logic A. If ANY column in thetable is updated, then I'd like logic B to be executed.Is it possible to just determine if only "insert" or an "update"ocurred from within the a single Trigger, without specifying eachindividual column name? (I.E. not saying IF udpate(col1) orupdate(col2) or ect...) Is it possible to just perform a check on theprocess that occurred, irregardless of column? Like If INSERTED =TRUE then execute insert logic. If UPDATED = TRUE, then run theupdated logic. I would like for all of this code to be stored withinthe same trigger.If anyone can provide some sample code on how to do this, if at allpossible, I would be much appreciative.Thanks,-RigsPS I know I could do this with 2 seperate triggers, but I'm trying toavoid that.
View Replies !
Trigger To Update An Updated Record?
I have a table called invoice. The invoice table has two audit columns: last_update_by and last_updated_date. I am trying to create an ON UPDATE trigger that will use the getdate() and user_name() functions and udpate the updated record with those two values. In other words, I want to create an ON UPDATE trigger on the invoice table that udpates the invoice table. Would this create an infinite loop? Can you update an updated record through the use of a trigger in this manner? Thanks for your help.
View Replies !
How Can I Get Which Columns Were Updated In Trigger On Update
Hi, I'm using sql-2005. I want to update several columns in different tables whenever an update is happend on specific table. I have a trigger for that which update all the relevant places when it fires. I want to do this update only if certains columns were changed, otherwise - do anything, to reduce performance. How Can I know which columns were updated inside the trigger? The tables has many columns and only if 2 of them were changed then I need to update other tables. Many thanks, Nira.
View Replies !
Trigger To Indicate The Row Has Been Changed (updated Or Inserted)
Hi, We have a column syncUpdate in some tables and we need a trigger (or one for each table) which will set the current dateTime for the syncLastUpdate (dateTime) when either the row is inserted or updated (we have to ignore the syncLastUpdate column itself as this would be an infinite loop, I think). I don't know much about DB but I think that is easly doable. Can anyone help me with that, please? Cheers
View Replies !
How To Determine Which Record Was Updated Using A Update Trigger?
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 Replies !
Create A Trigger To Update A Row That's Been Inserted Or Updated
Hi Apologies if this is a silly question I have a basic table "Customer" which has Id Address1 Address2 Address3 Town County Postcode SearchData After I insert or Update a row in this table I need to update the SearchData column with UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE) only for that Id I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself. Regards David
View Replies !
Last Date Stored Proc Updated???
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
View Replies !
|
TRACKER
