Date/user Created and date/User updated fields

Question

Date/user Created and date/User updated fields

james.mcallester

SSC Journeyman

Points: 90
More actions
February 29, 2008 at 7:08 pm

#179531

I'm wanting to automatically maintain two fileds (date and userid) for both the creation of a record and each time the record is updated.
Basically, before the record is inserted or updated I want to set the date and userid appropriately. With an AFTER INSERT trigger for the create, I must UPDATE ... SET CreateDate = GETDATE(), CreateUser = SYSTEM_USER. But this then triggers the AFTER UPDATE trigger which handles a similar process for the UpdateDate & UpdateUser fields.
After an INSERT is complete, I really want the UpdateDate/User fields to be null.
This feels like it should be so simple ... any help will be terrific!. Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

For CreateDate and CreateUser you want column defaults not an insert trigger. You can create the defaults using the GUI or with T-SQL. If the table already exists:

ALTER TABLE [table_name]

ADD CONSTRAINT DF_CreateDate DEFAULT getdate() FOR CreateDate

ALTER TABLE [table_name]

ADD CONSTRAINT DF_CreateUser DEFAULT Sytem_Uer() FOR CreateUser

or when creating the table

Create Table def_test

(

createuser varchar(25) Default system_user,

createdate datetime Default getdate()

)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

james.mcallester SSC Journeyman Points: 90 More actions · Answer 2

David, thanks. This is great.:)

It had crossed my mind, but then I started thinking about other situations in which I might want a field value conditionally defaulted.

In the past I've had a BEFORE INSERT trigger available to handle this.

Any ideas on this one?

Thanks, James

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I've never used one, but SQL Server has an Instead Of trigger that will probably allow you to do what you want. Basically the code in the Instead Of trigger fires in place of the statement that caused it to fire. Look in BOL for usage.

Who's David? I don't see a post by David.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

james.mcallester SSC Journeyman Points: 90 More actions · Answer 4

Jack

THere is no David ... my apologies ... it's Saturday here (what am I doing at work !?!?)

My brain is a bit slow ...

Thanks for the advice ... I'll give the INSTEAD OF a try.

Last question on this topic ...

For the update trigger to change the UpdateDate/User fields, I'm trying to use ...

UPDATE table SET updateDate = getdate(), Updateuser = SYSTEM_USER WHERE ID = @@IDENTITY.

Stupid, I know.

So what should the WHERE clause contain in this case where I want to change the record just updated?

TIA James

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Hey no problem, David is my middle name anyway.

You want your update trigger to join on the inserted table;

Update A

Set UpdateUser = system_user,

UpdateDate = getdate()

FRom

table A Join

inserted or deleted B On

A.primary_key = B.primary_key

This will also guarantee that all the rows in a mass update will get set as well.

Hey, it's late Friday night here and my wife is gone. I have nothing better to do than to try to better my standing on SSC.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

james.mcallester SSC Journeyman Points: 90 More actions · Answer 6

Well Jack David .... I must be psychic ... or at least psycho.

Glad I'm not impinging on your quality family time ...

Thanks so much for the advice ... I have two major projects needing this solution for impending production release to major clients and it was really bugging me.

Whatever happened to the INSERT/UPDATE BEFORE triggers .. or was that only SYbase???

Thanks again and take care. Have a beer for me.;)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

No problem, I enjoy pretending I am an expert. BTW-I was right on my answers.

Before triggers were never part of MS SQL Server. I know they are in Oracle, but I don't know about Sybase. The Instead Of trigger was added in SQL 2000 in an attempt to mimic BEFORE triggers. As I said I have never used Instead Of and have never really needed them or BEFORE triggers.

Have a good weekend and don't work too hard:hehe:

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 8

Just my 2 cents...

I agree... having a default on the "Created" columns does the job for those... but, there's a catch... what happens if someone tries to UPDATE those columns? And, what happens if someone tries to UPDATE the "Modified" columns?

Our rules at work are that no one can directly insert or update information into the "Created" or "Modified" columns... only the trigger can do that and that means that the trigger must be written to handle that particular problem. So having a default on the columns really doesn't buy you much if you really want to enforce the purpose of "CREATED" and "MODIFIED" columns especially for something like SOX.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

I generally have not had to worry about people modifying columns because I never, grant direct modify permissions to a table, I actually rarely grant select on tables. Everything is done through sp's that do not expose "system" columns. I know that there is always someone, like me, that has direct access, but anyone that has those rights can also disable triggers so the triggers aren't full proof either. I do agree that they are better though.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

That brings up an interesting point, Jack... I wonder if you can make a Trigger that has DDL Trigger associated with it and between the two, make it impossible even for the SA login to disable the pair? Talk about SOX compliant! :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

As I was typing that post I thought, can you disable DDL triggers? If not would the DDL trigger catch a disable trigger statement? Unfortunately there is always someone who has the rights to get to the data. Have you ever used C2 auditing?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 12

No... haven't used C2 Auditing... I forgot about that... might also be interesting to try.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)