Default value or Binding = (getdate()) in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Default value or Binding = (getdate()) in table

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-10 : 15:08:50

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

1421 Posts

Posted - 2009-03-10 : 15:13:36

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"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 15:13:40

How does your update statement look like?

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-10 : 15:15:50

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 14:13:52

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

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-11 : 14:41:48

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

bahiapt
Starting Member

6 Posts

Posted - 2013-05-27 : 19:03:43

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?

bahiapt
Starting Member

6 Posts

Posted - 2013-05-27 : 19:04:13

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-27 : 20:52:48

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

bahiapt
Starting Member

6 Posts

Posted - 2013-05-27 : 22:32:13

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 00:52:27

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

bahiapt
Starting Member

6 Posts

Posted - 2013-05-28 : 06:32:53

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 06:36:34

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

bahiapt
Starting Member

6 Posts

Posted - 2013-05-28 : 07:17:20

Hi Visakh16, i though that could be causing it, i will try out your sugestion.

Best regards,

Daniel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 07:46:10

ok
Let me know if you need any more help

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources