restrict update trigger to updated rows

How do I restrict an update trigger to just the rows updated?
Would like to have two audit fields with user and time of modification for
updates.
The following trigger would attempt to update all rows in the table:
ALTER trigger tm_stmp_admit_proc
on dbo.tblAdmissions_procedures
for update
as
begin
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
end
Couldn't find any description in BOL analogous to the FOR EACH ROW parameter
in
PostgreSQL:
CREATE OR REPLACE FUNCTION public.timestamper()
RETURNS trigger AS
'
BEGIN
NEW.time_stamp := \'now\';
NEW.user_modify := current_user;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER timestamper
BEFORE INSERT OR UPDATE
ON public.admit_procedures
FOR EACH ROW
EXECUTE PROCEDURE public.timestamper();
Thanks,
David P. Lurie
If you have a primary key column in the table
use INSERTED table in the trigger.
like
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
where exists ( select keycolumn from inserted
where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
but, then u may also have to set this parameter
SET RECURSIVE_TRIGGERS OFF
HTH
Faris
"David P. Lurie" <abc@.def.net> schrieb im Newsbeitrag
news:%23Di0ArVbEHA.2944@.TK2MSFTNGP11.phx.gbl...
> How do I restrict an update trigger to just the rows updated?
> Would like to have two audit fields with user and time of modification for
> updates.
> The following trigger would attempt to update all rows in the table:
> ALTER trigger tm_stmp_admit_proc
> on dbo.tblAdmissions_procedures
> for update
> as
> begin
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> end
> Couldn't find any description in BOL analogous to the FOR EACH ROW
parameter
> in
> PostgreSQL:
> CREATE OR REPLACE FUNCTION public.timestamper()
> RETURNS trigger AS
> '
> BEGIN
> NEW.time_stamp := \'now\';
> NEW.user_modify := current_user;
> RETURN NEW;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
> CREATE TRIGGER timestamper
> BEFORE INSERT OR UPDATE
> ON public.admit_procedures
> FOR EACH ROW
> EXECUTE PROCEDURE public.timestamper();
> Thanks,
> David P. Lurie
>
|||Thanks a lot -
I tested this on two of the tables, and seems to work. Didn't turn off
recursive triggers to see whether it was necessary, and no problems thus far
with a few updates on each table. Assume that infinite loop would have
occurred if that setting was needed.
David P. Lurie
"Faris" <faris@.bss-india.com> wrote in message
news:eTBHKhWbEHA.3944@.tk2msftngp13.phx.gbl...
> If you have a primary key column in the table
> use INSERTED table in the trigger.
> like
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> where exists ( select keycolumn from inserted
> where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
> but, then u may also have to set this parameter
> SET RECURSIVE_TRIGGERS OFF
> HTH
> Faris

Restoring to new database doesn't change logical DB name

I created a new database from a restore backup method. The database file names did get updated with the new database name, but the logical file name still remains the old file name.
Do we have to manually change the logical file name in the new database?
what is the use of logical name?
Please help!

The logical file will not change while u restore a db. you have to change it explicitly

alter database gpx mODIFY FILE (NAME=oldLogicalFilename,NEWNAME=NewLogicalFilename)

Madhu

|||

what is the use of logical name?

The name to represent the database data file. The logical name for each file is contained in the name column

The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can hinder the performance of the whole system.

Restoring database problem in SQL Server 7

I have an updated version of a database which was not created by a
backup on my server but on another server. I put the updated copy in a
directory, and attempt to restore it to a database on my system. No
matter what I do, even though I am pointing directly to the backup, SQL
Server is showing me a backup with the size and date/time of my last
backup of that same database and not the updated copy. The amazing thing
is that the backup file which I am trying to use to restore is pointed
to directly, while no previous backup which I have done is anywhere in
that same directory, and yet SQL Server is somehow restoring that
previous backup. Does anybody know what is going on here ? It just can
not be that hard to use another backup from the database on another
server to restore to my server.
Have you tried:
restore database MyDB
from disk = 'C:\MyDB.bak'
with replace
, move 'MyDataFile' to 'C:\MyDB.mdf'
, move 'MyLogFile' to 'C:\MyDB.ldf'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:u%231yYZNXFHA.2468@.TK2MSFTNGP10.phx.gbl...
I have an updated version of a database which was not created by a
backup on my server but on another server. I put the updated copy in a
directory, and attempt to restore it to a database on my system. No
matter what I do, even though I am pointing directly to the backup, SQL
Server is showing me a backup with the size and date/time of my last
backup of that same database and not the updated copy. The amazing thing
is that the backup file which I am trying to use to restore is pointed
to directly, while no previous backup which I have done is anywhere in
that same directory, and yet SQL Server is somehow restoring that
previous backup. Does anybody know what is going on here ? It just can
not be that hard to use another backup from the database on another
server to restore to my server.