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

Restrict access during a few hours

While we update our datawarehouse we don't want the users
to be able to connect to the database and update
their reports.
So we want to block access during certain hours.
What is the best way fix this?
The users use a SqlServer login with permission to read
data.Hi
It easiest would be to set it to dbo use only (assuming your users do not
have dbo rights)
ALTER DATABASE northwind
SET RESTRICTED_USER
Regards
Mike
"Leif" wrote:

> While we update our datawarehouse we don't want the users
> to be able to connect to the database and update
> their reports.
> So we want to block access during certain hours.
> What is the best way fix this?
> The users use a SqlServer login with permission to read
> data.
>

Restrict access during a few hours

While we update our datawarehouse we don't want the users
to be able to connect to the database and update
their reports.
So we want to block access during certain hours.
What is the best way fix this?
The users use a SqlServer login with permission to read
data.
Hi
It easiest would be to set it to dbo use only (assuming your users do not
have dbo rights)
ALTER DATABASE northwind
SET RESTRICTED_USER
Regards
Mike
"Leif" wrote:

> While we update our datawarehouse we don't want the users
> to be able to connect to the database and update
> their reports.
> So we want to block access during certain hours.
> What is the best way fix this?
> The users use a SqlServer login with permission to read
> data.
>

Restoring rows accidentaly modified

Hello!
How can I Restore rows which are accidentaly modified.
I forgetted to put where condition in "Update" Query.
Now I want my data back. I have not taken back up manually yet.
I am using SQL Server 2000.
Please help.
Thank you,There is a way, but none via sql. As far as I know, you'rebasically SoL. Transactions can be rolled back, but you can't doit after it's already been commited. There's applications outthere that supposedly can do it, but I can't for the life of meremember the names of them.
Sorry man.
|||

Tsk tsk tsk ... let this be a lesson.
1) ***NEVER*** develop a DML script (UPD,INS,DEL) in production -- that's what a dev database is for
2) Backup your database every night or if possible, more often

|||

Alex Papadimoulis wrote:

Tsk tsk tsk ... let this be a lesson.

1) ***NEVER*** develop a DML script (UPD,INS,DEL) in production -- that's what a dev database is for

Well, I would assert that there are times where a DML script isneeded in production, and you don't have the luxury of using a devdatabase first. My suggestion is to ALWAYS wrap it in atransaction, with a ROLLBACK at the end, until you obtain the desiredresults.

BEGIN TRANSACTION
UPDATE
someTable
SET
someColumn = 'someValue'
WHERE
someCondition = 'True'
ROLLBACK -- change to COMMIT once you know you are updating the correct records

I ALWAYS ALWAYS ALWAYS do this, lesson learned the hard way.