creating trigger to auto set create/modify dates

[efinney]

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;

[Hugo Kornelis]

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)