Trigger no longer populating a timestamp field with the current date 2

[smatthews]

We have triggers on 3 of our tables and when you change a column it gets dumped into 2 log tables by the triggers. When we upgraded from SQL2K to SQL2005 somehow the timestamp (which is actually the getdate()) is no longer being populated, it is now null. Not sure how that was working because the column "time_stamp" was not on the insert line of the trigger (see below.) Should I add the column into the insert and select statement or in SQL2005 is it better handled with a constraint? If a constraint is better can you share an example?

Thanks
Smatthews

originial trigger:

ALTER trigger [Name_Delete] on [dbo].[Name]
for delete as
begin
insert Log_Main(ACTION_TYPE,TABLE_NAME,KEY_CLUSTER)
select 4,'Name',ID
from deleted
end

would these new changes work or is there a better way:

ALTER trigger [Name_Delete] on [dbo].[Name]
for delete as
begin
insert Log_Main(TIME_STAMP,ACTION_TYPE,TABLE_NAME,KEY_CLUSTER)
select GETDATE(),4,'Name',ID
from deleted
end

 

[gmmastros]

If the TIME_STAMP column in the Log_Main table has a default value, then you shouldn't need to change the trigger code.

You can tell if there is a default value for the column by running this code:

Select Column_Default 
From   Information_Schema.Columns
Where  Table_Name = 'Log_Main'
       And Column_Name = 'TIME_STAMP'

You should see exactly one row in the output and it should look something like "(GetDate())" If it does not, let me know what the output is.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

Thanks for responding George.

The output is null

 

[djj55]

Not knowing how you moved the tables the constraint may have been dropped (missed).

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[smatthews]

Can you show me the best way to add the constraint? And how?

Thanks

 

[djj55]

Hope this helps.

ALTER TABLE [dbo].[mytable] ADD  CONSTRAINT [NameofConstraint]  DEFAULT (getdate()) FOR [mycolumn]
GO

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[smatthews]

The constraint worked I return getdate() when I run:

Select Column_Default
From Information_Schema.Columns
Where Table_Name = 'Log_Main'
And Column_Name = 'TIME_STAMP'

but the time_stamp field is still null when I enter new changes.

Any ideas?

Thanks

 

[gmmastros]

What does your trigger code look like?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

So sorry...was looking at an old query...looks great! Thanks for hanging in with me.

 

[smatthews]

It appears that we lost the user name who was logged in at the time too. This must have also been a constraint. Is it possible to pull the sql user who make the change as a constraint?

Thanks.

 

[gmmastros]

Try this...

ALTER TABLE [dbo].[YourTableName] ADD  CONSTRAINT [NameofConstraint]  DEFAULT (suser_sname()) FOR [YourColumnNameHere]
GO

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

Thank you.