May 7, 2009 at 3:33 pm
Hi there,
I have a table with a TIMESTAMP column. On every new record, I want to set this column value to the CURRENT_TIMESTAMP using an INSERT trigger. Column name is REGISTRADO.
create trigger BI_ORDENTMP
on ORDENTMP
for INSERT
as
BEGIN
-- What should I put in here?
END
Thanks in advance.
May 7, 2009 at 4:02 pm
I think you are a bit confused on default and timestamps.
the "timestamp" datatype is poorly named, and should be referenced by it's synonym called rowversion is a special datatype (you cannot insert a value in it)
it gets a unique value for the insert, but it is not related to time.
an identity column auto-increments for each row that gets inserted, similar to the timestamp, except it is typically an integer and not a hex value.
finally a default is a constraint that inserts a specific value if you do not supply it on the insert...the example below inserts the getdate()(SQL Servers date) /CURRENT_TIMESTAMP(ANSI standard for the same thing), so you never need a trigger to populate any of these three items:
Create Table example(
exampleId int identity(1,1) not null primary key,
exampletext varchar(30),
REGISTRADO rowversion, --can also be called timestamp, albiet it is porly named
MyInsertedTime datetime default getdate() ) --you can replace getdate() with current_timestamp...same thing
insert into example(exampletext)
SELECT 'one' union all select 'two'
select * from example
--results:
exampleId exampletext REGISTRADO MyInsertedTime
1 one 0x0000000000001649 2009-05-07 17:54:38.780
2 two 0x000000000000164A 2009-05-07 17:54:38.780
Lowell
May 7, 2009 at 4:10 pm
Thanks for your example. I'm getting the idea.
But what if I would need to populate the MyInsertedTime column using an INSERT trigger anyway? Suppose I forgot to add the DEFAULT getdate() constraint to the column and there is no way to add it later. I juts ask for learning purposes.
May 7, 2009 at 4:51 pm
you could still add a default constraint later...but that's beside the point.
you are right, you might need to do something in a trigger that is not quite doable in a default constraint...
ok sure...here's an example...
inside a trigger, there are two virtual tables named INSERTED and DELETED, which contains the new values if inserted or updated, and the old value if updated or deleted...
the two virtual tables have the same column names as the table it is a trigger on.
hopefully it is obvious that the DELETED table would have no data during an Insert.
you use the UPDATE.. FROM syntax to update your table, joining it on the columns() that make each row unique.
--building on the same table used previously
ALTER TABLE EXAMPLE ADD MYOTHERCOLUMN DATETIME
ALTER TABLE EXAMPLE ADD FOLLOWUPDATE DATETIME
GO
CREATE TRIGGER MY_TRIGGER ON EXAMPLE
FOR INSERT
AS
BEGIN
UPDATE EXAMPLE
SET MYOTHERCOLUMN = CURRENT_TIMESTAMP,
FOLLOWUPDATE = CURRENT_TIMESTAMP + 7 --add exactly 7 days...follow up in one week
FROM INSERTED
WHERE EXAMPLE.EXAMPLEID = INSERTED.EXAMPLEID --note how if there are 100 rows in inserted, all 100 get updated...
END
GO
insert into example(exampletext)
SELECT 'three' union all select 'four'
select * from example
--results
exampleId exampletext REGISTRADO MyInsertedTime MYOTHERCOLUMN FOLLOWUPDATE
1 one 0x000000000000164D 2009-05-07 18:04:33.187 NULL NULL
2 two 0x000000000000164E 2009-05-07 18:04:33.187 NULL NULL
3 three 0x0000000000001651 2009-05-07 18:49:24.750 2009-05-07 18:49:24.780 2009-05-14 18:49:24.780
4 four 0x0000000000001652 2009-05-07 18:49:24.750 2009-05-07 18:49:24.780 2009-05-14 18:49:24.780
GO
Lowell
May 7, 2009 at 5:03 pm
Lowell (5/7/2009)
you could still add a default constraint later...but that's beside the point.
FYI, MS now calls it a default rule, and not a constraint.
Excellent response to the poster.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply