SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 create trigger fr use column datetime AS timestamp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barcelo
Starting Member

20 Posts

Posted - 10/28/2013 :  12:21:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 10/28/2013 :  13:13:41  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/28/2013 :  13:14:48  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 10/28/2013 :  13:20:18  Show Profile  Reply with Quote
Strike my brilliant code up above. It is crap, to put it mildly :)
Go to Top of Page

barcelo
Starting Member

20 Posts

Posted - 10/28/2013 :  16:08:48  Show Profile  Reply with Quote
[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!?



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/28/2013 :  23:58:24  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000