create trigger fr use column datetime AS timestamp

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)

create trigger fr use column datetime AS timestamp

Author

Topic

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 12:21:20

How I can create a trigger for use a column datetime as timestamp

Example column "updated":
Columns Employee Table:
---------------------------------------
- Id_Employee.......(vachar(7), NO NULL)
- Date.............(smalldatetime, NO NULL)
- IN...............(smalldatetime, NULL)
- OUT..............(smalldatetime, NULL)
- updated..........(datetime, NO NULL).....(DEFAULT VALUE GETDATE())

So, I need a trigger to automatically update the value of the "updated" whenever you change the value of any other field in the row to which it belongs

Employee <------TABLE 1
---------------------------------------------
Id_Employee--Order---IN-----OUT------Date-------updated

21866........1......07:55..*12:05*...4/10/2013.."4/10/2013 *12:05*"///Updated

look data into **
21866........1......*07:55*.."NULL"..4/10/2013.."4/10/2013 *07:55*"///Inserted

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 13:13:41

Something like this - I am using dummy names - use your real table name etc

CREATE TRIGGER dbo.YourTableUpdateTrigger
ON YourTable FOR UPDATE, INSERT
as
UPDATE INSERTED SET updated = GETDATE();

Just one caveat. Run the following on your database to make sure that recursive triggers is OFF on your database. It is off by default, so it likely is, but please do check.

EXEC sp_dboption 'YourdatabaseNameHere', 'recursive triggers'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 13:14:48

[code]
CREATE TRIGGER ApplyDefault
ON Employee
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(updated)
UPDATE e
SET e.updated = DEFAULT
FROM Employee e
INNER JOIN INSERTED i
ON i.Id_Employee = e.Id_Employee
AND i.Order = e.Order
END
[/code]

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 13:20:18

Strike my brilliant code up above. It is crap, to put it mildly :)

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 16:08:48

[quote]Originally posted by visakh16


CREATE TRIGGER ApplyDefault
ON Employee
AFTER UPDATE
AS
BEGIN 
IF NOT UPDATE(updated)
  UPDATE e
  SET e.updated = DEFAULT
  FROM Employee e
  INNER JOIN INSERTED i
  ON i.Id_Employee = e.Id_Employee
  AND i.Order = e.Order
END

The trigger affect more of one rows when a row is updated
look:
(76 rows affected) the trigger affect more rows
(1 rows affected) Consult for update one row

I need that only affect field "updated" in the row updated.

I think the problem is in:
..
ON i.Id_Employee = e.Id_Employee
AND i.Order = e.Order
....

It affects all table rows with the same Id_Employee AND Order

It would be better to control the trigger for the date?

since for example:

Employee.Date = "28/10/2013"

will only have one row with Order 1 AND/OR Order 2 for a same Id_Employee.

Id_Employee--Order---IN-----OUT------Date-------updated
21866........1......13:00..17:15...28/10/2013.."28/10/2013 12:05"
21866........2......07:55..12:05...28/10/2013.."28/10/2013 12:05"

Undertand!?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 23:58:24

it depends on what your primary key combination of table is. You've not specified it in your post. Thats why I assumed its Id_Employee + Order as i saw more than one record from same Id_Employee

Also as per
will only have one row with Order 1 AND/OR Order 2 for a same Id_Employee.
my join should work fine

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