Trigger Date Creation, Update and User

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 2008 Forums

Transact-SQL (2008)

Trigger Date Creation, Update and User

Author

Topic

andreacaffy
Starting Member

6 Posts

Posted - 2011-10-05 : 14:54:44

Hi, I need your help.
I Have a database with about 100 tables. Every table has 4 fields:
DT_UM date update
DT_CR date creation
UT_UM user that makes update of record
UT_CR user that creates record

These tables are linked to MSAccess through ODBC driver.

How can I build a trigger or how can I do this?

I would like to build a procedure that automatically makes these operations.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-05 : 19:54:05

The two creation values might better be done as DEFAULT constraints. If you need this to be bulletproof you'll still need the triggers to make sure no one is changing these columns.
Having said that, you can use a script that will generate the proper CREATE TRIGGER statements as long as you can determine what the primary keys of your tables are.[CODE]select
'create trigger dbo.trU_' + t.Name + ' on ' + t.Name + CHAR(13) + CHAR(10) +
'FOR UPDATE' + CHAR(13) + CHAR(10) +
'as' + CHAR(13) + CHAR(10) +
'update t' + CHAR(13) + CHAR(10) +
'set DT_UM = GetDate(),' + CHAR(13) + CHAR(10) +
' UT_UM = User_Name()' + CHAR(13) + CHAR(10) +
'from ' + t.Name +' t' + CHAR(13) + CHAR(10) +
'inner join inserted i' + CHAR(13) + CHAR(10) +
'on i.XXX = t.XXX -- Your logic goes here' + CHAR(13) + CHAR(10) +
'GO'
from
sys.tables t
inner join
sys.columns c1
on c1.object_id = t.object_id
and c1.name = 'DT_UM'
inner join
sys.columns c2
on c2.object_id = t.object_id
and c2.name = 'UT_UM'
order by t.name[/CODE]If you can determine the primary keys, modify this script to add that logic. In either case run the script in Management Studio with "Results to Text" mode active. The output is either ready to run or you will need to manually add the appropriate JOIN logic where indicated.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

andreacaffy
Starting Member

6 Posts

Posted - 2011-10-06 : 03:11:23

Thank you very much. I try.

But I have a question about users.
The function user_name(id) is a sql function che works on table sysusers.
My users are record of T_USERS table and when I login into access I insert my password.
How can I use these users and pass them to my tables?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-06 : 11:13:36

How do you know which user from your T_Users table is the active SQL user? Once you have that, you can join the T_Users table on that criteria:[CODE]INNER JOIN T_Users u on u.MySqlUser = User_Name()[/CODE]From there you use the T_Users value to populate the UT_UM field:[CODE]UT_UM = u.MyUserName[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources