New to Triggers

[Dave]

I'm trying to get a grasp on triggers, and that brings me here. I
have a series of tables with a 'Modby' field and a 'ModDate' field.
In the Default Value Property I have the (suser_sname()) and
(GetDate()) functions. Is what I'm after is a trigger that will
update these fields, for a specific record, if/when the record has
been modified.

I appreciate your help, and Thanks in advance.

[Anith Sen]

If you already have defaults, there is no need for triggers. You can use the
DEFAULT keyword in your INSERT & UPDATE DMLs like:

INSERT tbl (..., Modby, ModDate) VALUES (..., DEFAULT, DEFAULT);

UPDATE tbl
SET ....
ModBy = DEFAULT,
ModDate = DEFAULT
WHERE ...;

--
-- Anith
( Please reply to newsgroups only )

[Dave]

First, thanks for your reply. Second, if I'm correct, the default
value only applies to new records should no value be pleased in that
field. Also, what differentiates one record from the whole table? I
need to only update the User and Date for the record that's been
updated.
"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:<FN******************@newsread2.news.atl.eart hlink.net>...

If you already have defaults, there is no need for triggers. You can use the
DEFAULT keyword in your INSERT & UPDATE DMLs like:

INSERT tbl (..., Modby, ModDate) VALUES (..., DEFAULT, DEFAULT);

UPDATE tbl
SET ....
ModBy = DEFAULT,
ModDate = DEFAULT
WHERE ...;

[Anith Sen]

>> Second, if I'm correct, the default value only applies to new records
should no value be pleased in that field. <<

No, you can use it in your UPDATE statement as I have shown in my post.

Also, what differentiates one record from the whole table? <<

You have to use a proper WHERE clause to identify the rows which are
affected by the UDPATE statement.

--
-- Anith
( Please reply to newsgroups only )

[Dave]

Anith,
I appreciate you working with me on this, but I need for you to dumb
it down a little further. If I'm trying to update a record that has
been changed, what kind of where condition would I be using? What
signifies that the record has changed?
"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:<4T******************@newsread1.news.atl.eart hlink.net>...

Second, if I'm correct, the default value only applies to new records should no value be pleased in that field. <<

No, you can use it in your UPDATE statement as I have shown in my post.
Also, what differentiates one record from the whole table? <<

You have to use a proper WHERE clause to identify the rows which are
affected by the UDPATE statement.

[Anith Sen]

Dave,

Actually I was suggesting you, NOT to use a trigger at all, instead use the
DEFAULT keyword in your INSERT & UPDATE statements directly. So when you do
your UDPATE, you simply do:

UPDATE tbl
SET ....
ModBy = DEFAULT,
ModDate = DEFAULT
WHERE ...;

If you have a WHERE clause it will limit the number of rows based on the
WHERE clause condition & the DEFAULT will be effective for those rows only.
If you do not use a WHERE clause, all rows are updated & the DEFAULT is
applicable for all the rows in the table. Is that making sense?

--
-- Anith
( Please reply to newsgroups only )

[Dave]

Anith,

1.) Will the statement that you've suggested work as is? In other
words, copy & paste?

2.) Does that update all records or just the one being edited?

NOTE TO AUTHORS: Someone needs to write a book..."Triggers for Geek
Wanna-be's"

Thanks for your help Anith.
"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:<yO*******************@newsread2.news.atl.ear thlink.net>...

Dave,

Actually I was suggesting you, NOT to use a trigger at all, instead use the
DEFAULT keyword in your INSERT & UPDATE statements directly. So when you do
your UDPATE, you simply do:

UPDATE tbl
SET ....
ModBy = DEFAULT,
ModDate = DEFAULT
WHERE ...;

If you have a WHERE clause it will limit the number of rows based on the
WHERE clause condition & the DEFAULT will be effective for those rows only.
If you do not use a WHERE clause, all rows are updated & the DEFAULT is
applicable for all the rows in the table. Is that making sense?