SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Default value or Binding = (getdate()) in table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

536 Posts

Posted - 03/10/2009 :  15:08:50  Show Profile  Reply with Quote
I am using Default value or Binding = (getdate()) in all of my tables, where ever a updated field is:

I am facing a problem, when an existing record is updated i want the date to be changed.

The date is being plugged by SQL server only when a record is created in the table, not when that record is updated.

Is there a way to have the new current date when a record is updated.

Thank you very much for the information.

mfemenel
Professor Frink

USA
1421 Posts

Posted - 03/10/2009 :  15:13:36  Show Profile  Visit mfemenel's Homepage  Reply with Quote
1. Change the code that is updating to include an update to the date field.
2. Trigger

Mike
"oh, that monkey is going to pay"
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 03/10/2009 :  15:13:40  Show Profile  Reply with Quote
How does your update statement look like?
Go to Top of Page

cplusplus
Aged Yak Warrior

536 Posts

Posted - 03/10/2009 :  15:15:50  Show Profile  Reply with Quote
Sodeep, I defined this directly in the Table.
there is a property for fields Default Value or binding.

I am just typing Getdate() in it.

Thats all i am doing.

quote:
Originally posted by sodeep

How does your update statement look like?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/11/2009 :  14:13:52  Show Profile  Reply with Quote
quote:
Originally posted by cplusplus

Sodeep, I defined this directly in the Table.
there is a property for fields Default Value or binding.

I am just typing Getdate() in it.

Thats all i am doing.

quote:
Originally posted by sodeep

How does your update statement look like?




the default value will be applied only while inserting new records, for your updates to get value either pass getdate() explicitly in updatestaeement or create an update trigger like this

CREATE TRIGGER SetUpdatedDate
ON YourTable
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t.UpdateDate=GETDATE()
FROM YourTable t
JOIN INSERTED i
ON i.PK =t.PK
END


pk is your primary key
Go to Top of Page

cplusplus
Aged Yak Warrior

536 Posts

Posted - 03/11/2009 :  14:41:48  Show Profile  Reply with Quote
Thank you Visakh..

quote:
Originally posted by visakh16

quote:
Originally posted by cplusplus

Sodeep, I defined this directly in the Table.
there is a property for fields Default Value or binding.

I am just typing Getdate() in it.

Thats all i am doing.

quote:
Originally posted by sodeep

How does your update statement look like?




the default value will be applied only while inserting new records, for your updates to get value either pass getdate() explicitly in updatestaeement or create an update trigger like this

CREATE TRIGGER SetUpdatedDate
ON YourTable
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t.UpdateDate=GETDATE()
FROM YourTable t
JOIN INSERTED i
ON i.PK =t.PK
END


pk is your primary key

Go to Top of Page

bahiapt
Starting Member

6 Posts

Posted - 05/27/2013 :  19:03:43  Show Profile  Reply with Quote
Hi there, i am late 3 years for this post, but would appreciate if you could help me with the trigger, i tried to use it but it keeps giving me the message below:

.Net SqlClient Data Provider: Msg 209, Level 16, State 1, Procedure SetUpdatedDate, Line 30
Ambiguous column name 'ID_Cliente'.
.Net SqlClient Data Provider: Msg 209, Level 16, State 1, Procedure SetUpdatedDate, Line 30
Ambiguous column name 'ID_Cliente'.


Any idea of what it is?
Go to Top of Page

bahiapt
Starting Member

6 Posts

Posted - 05/27/2013 :  19:04:13  Show Profile  Reply with Quote
Also here is the code:

CREATE TRIGGER SetUpdatedDate
ON [Ficha de Cliente]
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t = GETDATE()
FROM [Ficha de Cliente]
JOIN INSERTED i
ON i.ID_Cliente = t.[ID_Cliente]
END
GO
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/27/2013 :  20:52:48  Show Profile  Reply with Quote
quote:
Originally posted by bahiapt

Hi there, i am late 3 years for this post, but would appreciate if you could help me with the trigger, i tried to use it but it keeps giving me the message below:

.Net SqlClient Data Provider: Msg 209, Level 16, State 1, Procedure SetUpdatedDate, Line 30
Ambiguous column name 'ID_Cliente'.
.Net SqlClient Data Provider: Msg 209, Level 16, State 1, Procedure SetUpdatedDate, Line 30
Ambiguous column name 'ID_Cliente'.


Any idea of what it is?

When you want to ask a question, it is better to start a new thread, and provide a link to the old thread if you do want to refer back to it.

Regardless, your trigger just doesn't seem syntactically right - it needs an alias for the [Ficha de Cliente] table/view as shown below. But, in addition, the .Net message you are seeing does not seem consistent with that error, so there may be other issues. Also, usually I try to avoid updating the same table in a trigger for that table - the reason being that if the nested trigger and recursive trigger options are changed, the trigger may have unintended consequences:
CREATE TRIGGER SetUpdatedDate
ON [Ficha de Cliente]
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t = GETDATE()
FROM [Ficha de Cliente]  as t
JOIN INSERTED i
ON i.ID_Cliente = t.[ID_Cliente]
END
GO
Go to Top of Page

bahiapt
Starting Member

6 Posts

Posted - 05/27/2013 :  22:32:13  Show Profile  Reply with Quote
Hi James, will do next time.

Thanks for the help so far but still need a bit more if you can.

I managed to install the trigger, and it works with a small caveat :).

What happens is that every time i make a change in a record in access the following message appears:

"The data has been changed.
Another User edited this record and saved the changes before you attempted to save your changes.
Re-edit the record."

After that i receive two rows on a table (every time a change is done) that i have set up to keep the changes on the records, activated by other trigger.

The time although works perfectly changes every time.

Any thoughts on this?


(Below code used for the trigger)
USE [BaseDadosSegurosTest1]
GO
/****** Object: Trigger [dbo].[SetUpdatedDate] Script Date: 05/28/2013 03:11:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[SetUpdatedDate]
ON [dbo].[Ficha de Cliente]
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t.[Data Transacao] = GETDATE()
FROM [Ficha de Cliente] as t
JOIN INSERTED i
ON i.[Data Transacao] = t.[Data Transacao]
END

Edited by - bahiapt on 05/27/2013 22:35:52
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/28/2013 :  00:52:27  Show Profile  Reply with Quote

After that i receive two rows on a table (every time a change is done) that i have set up to keep the changes on the records, activated by other trigger


which is the other trigger?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bahiapt
Starting Member

6 Posts

Posted - 05/28/2013 :  06:32:53  Show Profile  Reply with Quote
Hi Visakh16,


sending below trigger that i use to audit the table for changes:

USE [BaseDadosSegurosTest1]
GO
/****** Object: Trigger [dbo].[Tr_FichaDeCliente_ForUpdate] Script Date: 05/28/2013 11:32:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[Tr_FichaDeCliente_ForUpdate]
ON [dbo].[Ficha de Cliente]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here


INSERT INTO [Aud_FichaDeCliente]
Select * From DELETED


END





quote:
Originally posted by visakh16


After that i receive two rows on a table (every time a change is done) that i have set up to keep the changes on the records, activated by other trigger


which is the other trigger?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/28/2013 :  06:36:34  Show Profile  Reply with Quote
Hmm...two update triggers on same table?

why not wrap all logic inside single trigger?


USE [BaseDadosSegurosTest1]
GO
/****** Object: Trigger [dbo].[SetUpdatedDate] Script Date: 05/28/2013 03:11:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[SetUpdatedDate]
ON [dbo].[Ficha de Cliente]
AFTER UPDATE
AS
BEGIN
IF NOT (UPDATE([Data Transacao]))
BEGIN
UPDATE t
SET t.[Data Transacao] = GETDATE()
FROM [Ficha de Cliente] as t
JOIN INSERTED i
ON i.[Data Transacao] = t.[Data Transacao]
END

INSERT INTO [Aud_FichaDeCliente]
Select * From DELETED
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bahiapt
Starting Member

6 Posts

Posted - 05/28/2013 :  07:17:20  Show Profile  Reply with Quote
Hi Visakh16, i though that could be causing it, i will try out your sugestion.

Best regards,

Daniel
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/28/2013 :  07:46:10  Show Profile  Reply with Quote
ok
Let me know if you need any more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000