creating trigger to auto set create/modify dates

Hi,

I'm a newbie to sql server and this may be a really dumb question for
some you. I'm trying to find some examples of sql server triggers that
will set columns (e.g. the created and modified date columns) if the row
is being inserted and set a column (e.g. just the modified date column)
if the row is being updated.

I know how to do this in oracle plsql. I would define it as a before
insert or update trigger and reference old and new instances of the
record. Does sql server have an equivalent? Is there a better way to do
this in sql server?

Thanks
eric

this is what i do in oracle that i'm trying to do in sqlserver...

CREATE OR REPLACE TRIGGER tr_temp_biu
before insert or update
on temp
referencing old as old new as new
for each row
begin
if inserting then
:new.created_date := sysdate;
end if;
:new.modified_date := sysdate;
end tr_temp_biu;On Thu, 07 Oct 2004 13:02:41 -0400, efinney wrote:

>Hi,
>I'm a newbie to sql server and this may be a really dumb question for
>some you. I'm trying to find some examples of sql server triggers that
>will set columns (e.g. the created and modified date columns) if the row
>is being inserted and set a column (e.g. just the modified date column)
>if the row is being updated.
>I know how to do this in oracle plsql. I would define it as a before
>insert or update trigger and reference old and new instances of the
>record. Does sql server have an equivalent? Is there a better way to do
>this in sql server?
>Thanks
>eric
>this is what i do in oracle that i'm trying to do in sqlserver...
(snip)

Hi Eric,

Don't try to do a one on one translation from Oracle to SQL Server. The
differences are too big (especially when it comes to triggers and other
non-ANSI-standard syntax).

For the creation date, no trigger is needed. Simply use a default. Use the
same default for the modified date if you want that set at the time a row
is inserted as well. If you rather leave the moodified_date NULL until the
row actually is updated, remove the default and change NOT NULL to NULL.

CREATE TABLE MyTable (KeyCol1 int NOT NULL,
KeyCol2 char(6) NOT NULL,
DataCol1 varchar(130) NULL,
DataCol2 datetime NOT NULL,
Created_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
Modified_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
PRIMARY KEY (KeyCool1, KeyCol2)
)

There are no BEFORE triggers in SQL Server. Only AFTER and INSTEAD OF
triggers are supported. In your case, I'd use an AFTER trigger. A very
fundamental difference that you should really be aware of, is that SQL
Server executes a trigger once per update (insert, delete) statement, with
all affected rows in the inserted and deleted pseudo-tables. All Oracle
triggers I've seen so far are processed for each individual row - a very
big difference that can lead to spectacular errors.

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
-- Prevent recursion!
IF NOT UPDATE(Modified_Date)
BEGIN
UPDATE MyTrigger
SET Modified_Date = CURRENT_TIMESTAMP
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.KeyCol1 = MyTable.KeyCol1
AND i.KeyCol2 = MyTable.KeyCol2)
END

(untested)
UPDATE MyTable

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating rows based on date range from another table

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
>
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
><...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
><...>
200602 2005-12-18 75
>
CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
>
INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)

Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

Given this table, we can write this query:

SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob

Erland Sommarskog wrote:

Quote:

Originally Posted by

rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

>
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
>
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
>
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
>
Given this table, we can write this query:
>
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

Quote:

Originally Posted by

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to build a table based on values from another table.
>I need to populate a table between two dates from another table. Using
>the START_DT and END_DT, create records between those dates.
>I need a new column that is the days between the date and the MID_DT
>The data I wish to end with would look something like this:
>>
>PERIOD DATE DAY_NO
>200602 2005-07-06 -89
>200602 2005-07-07 -88
>200602 2005-07-08 -87
>><...>
>200602 2005-10-02 -2
>200602 2005-10-03 -1
>200602 2005-10-04 0
>200602 2005-10-05 1
>><...>
>200602 2005-12-18 75

[snip]

Quote:

Originally Posted by

Quote:

Originally Posted by

>INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)

Quote:

Originally Posted by

>Anyway, as I said in another newsgroup, you need a table of numbers. Here
>is a way to create such a table with a million numbers:

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.

The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.

A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What are the pros and cons of relying on such a table vs. using a
>WHILE loop? Based on Rob's context of student registrations, let's
>assume we're talking about a maximum of 300 iterations per row in
>the original tblDates table.

>
>The one risk with a table of numbers is that if you run of numbers, you
>will get an incorrect result. That is one reason why I'm reluctant to
>use it, if there are alternative solutions. But for a case like this,
>when you need to fill up a space, a table of numbers - or dates - is what
>you need.
>
>A loop is more complex to program, and easier go wrong.

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:

x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while

versus

select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)

Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Quote:

Originally Posted by

And as a generic
>solution, you face scalability problems.

I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_date, n) between first_date and last_date", but that
seems like it'd be a good bit slower.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
>
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
>
versus
>
select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)
>
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Loops are particularly prone to two sorts of errors:

* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.

One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Creating dates from table columns

Hi, i have a table with 3 ints that are used to store dates. The
datetime data type is not used because this data comes from an old
AS400 server.

I need to be able to use those 3 columns to build dates within a query
and be able to use them to compare themselves to other dates

Let's say the table has the following values:

myday mymonth myyear
23 5 2006

and suppose i want to do a query that displays all rows with date
greater than '20060520'

Here is the query i have tried:

select
cast(myday as varchar(2))+'/'+cast(mymonth as
varchar(2))+'/'+cast(myyear as varchar(4))
from mytable

That query returns the string '23/5/2006' yet i can't use it to compare
it with '20060520'

Is there a way i can do this in a simple query?
This is on sql server 2000This seems to work but it is way ugly and depends heavily on the date
format:

select
cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
from mytable
where cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
between '20060520' and '20061231'

Also, i tried to use an alias on the select but then it doesn't get
recognized on the where so i had to type it full again.

Anyone knows a better way? Asp has a dateserial() method that
constructs a date given a month , day and year, but sql server doesn't
seem to have anything like that as a sql date function.|||fjleon@.gmail.com wrote:
> Hi, i have a table with 3 ints that are used to store dates. The
> datetime data type is not used because this data comes from an old
> AS400 server.

You mean via a linked server or do you import it? If it's the latter
then preferably fix the dates at import before they get into the
database.

Try:

SELECT myday, mymonth, myyear,
DATEADD(DAY,myday,
DATEADD(MONTH,mymonth,
DATEADD(YEAR,myyear-2000,'19991231')))
FROM mytable ;

This returns a DATETIME, not a string.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||> Try:
> SELECT myday, mymonth, myyear,
> DATEADD(DAY,myday,
> DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myyear-2000,'19991231')))
> FROM mytable ;
> This returns a DATETIME, not a string.

Seems to be one month forward:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

10520062006-06-10 00:00:00.000
20520062006-06-20 00:00:00.000

I had to substract a month:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

I am guessing this only works from y2k forward. I don't quite
understand why i have to substract a month, but at least it works, and
it's a lot cleaner than casting around.|||Do you know how to use that constructed date in a WHERE? dateadd....
as mydate where '20060501' <=p for example doesn't work

The weird thing is that in sql analizer it shows mydate as the name of
the column but if i use it in the where it fails|||(fjleon@.gmail.com) writes:
> Seems to be one month forward:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> 10 5 2006 2006-06-10 00:00:00.000
> 20 5 2006 2006-06-20 00:00:00.000
> I had to substract a month:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> I am guessing this only works from y2k forward.

No, it works for dates in the 1900s as well.

> I don't quite understand why i have to substract a month,

When you've added 5 months, you are at the end of May. Now you
add some days. That brings you into June.

> Do you know how to use that constructed date in a WHERE? dateadd....
> as mydate where '20060501' <=p for example doesn't work
> The weird thing is that in sql analizer it shows mydate as the name of
> the column but if i use it in the where it fails

It's not weird at all. You cannot use a column alias defined in a query
anywhere else in the query, except in the ORDER BY clause. However, you
can use a derived table - a query within the query. See the script
below. I've also modofied David's expression in a way that I think is
more robust.

CREATE TABLE fjleon(myday int NOT NULL,
mymonth int NOT NULL,
myyear int NOT NULL)

INSERT fjleon (myday, mymonth, myyear)
VALUES (23, 5, 2006)
INSERT fjleon (myday, mymonth, myyear)
VALUES (12, 7, 1996)
INSERT fjleon (myday, mymonth, myyear)
VALUES (29, 2, 2004)
INSERT fjleon (myday, mymonth, myyear)
VALUES (1, 3, 2004)
go
SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon
go
SELECT myday, mymonth, myyear, mydate
FROM (SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon) AS x
WHERE mydate > '20040101'
go
DROP TABLE fjleon

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you both, this works nice.