Update trigger to prevent multiple row updates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update trigger to prevent multiple row updates

Author

Topic

ghaza
Starting Member

3 Posts

Posted - 2004-12-21 : 06:24:39

Hey guys

I was wondering if it is possible to create an update trigger that "blocks" and update statement if that statement is trying to update more than 1 row in the table but allows a single row to be updated.

The trigger should only "fire" if a specific field is included in the update, i.e for field "price below".

CREATE TABLE [dbo].[tblPrice] (
[ID] [int] NOT NULL ,
[FromDate] [smalldatetime] NOT NULL ,
[ToDate] [smalldatetime] NOT NULL ,
[Price] [int] NOT NULL ,
[PriceExtra] [int] NOT NULL ,
[PricePhoto] [int] NOT NULL
) ON [PRIMARY]
GO

"update tblprice set price = 100" should not be allowed
"update tblprice set price = 100 where priceextra = 20" should not be allowed
"update tblprice set price = 100 where id = 3451" should be allowed
"update tblprice set pricephoto = 35" should be allowed

Does this make sense?

I am no good with advanced sql and triggers so all help would be appreciated bigtime!

Thanks,

Peter

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-12-21 : 07:19:03

Peter
Use stored procedures to control what can be updated

CREATE PROC MyProc @Id int, @Price int
AS
UPDATE tblprice
SET price = @Price
WHERE [Id] = @Id --Assuming that the ID is unique

EXEC MyProc 3451, 100

Also from your requirements the following would update ALL records and not just a single record

quote:
update tblprice set pricephoto = 35

Question, how are users manipulating the data?

Andy

CLages
Posting Yak Master

116 Posts

Posted - 2004-12-21 : 08:24:31

Peter , i am using in this way, works fine to me
in the main table i have a Mov_trigger Column and if i want to fire a trigger i put something in this column,
in this case , if mov_trigger <> 'C' the trigger wil be fired

Carlos Lages

/****** Object: Trigger dbo.SomaSaldomov Script Date: 12/01/2004 13:46:28 ******/
CREATE TRIGGER [SomaSaldomov] ON dbo.moviment
FOR INSERT
AS
IF (SELECT mov_trigger FROM INSERTED) <> 'C' /*ou seja nao e um Lcto de atualizacao do Pedido*/
IF (SELECT mov_estoque FROM INSERTED) = 1 /*Estoque Oficial*/
if (SELECT mov_flag FROM INSERTED) = 1 /* Entrada entao Soma */
update produto
set
pro_pr_custo = i.mov_custo,
pro_saldo_fis1 = pro_saldo_fis1 + i.mov_qt + i.mov_qtvale,
pro_saldo_fin1 = pro_saldo_fin1 + i.mov_mercadoria + i.mov_vlvale from inserted i
where
pro_deposito = i.mov_deposito and
pro_codigo = i.mov_codigo
else /* entao Subtrai se for Saida */
update produto
set
pro_saldo_fis1 = pro_saldo_fis1 - i.mov_qt - i.mov_qtvale,
pro_saldo_fin1 = pro_saldo_fin1 - i.mov_mercadoria - i.mov_vlvale from inserted i
where
pro_deposito = i.mov_deposito and
pro_codigo = i.mov_codigo
else /* Estoque nao Oficial*/
if (SELECT mov_flag FROM INSERTED) = 1 /* Entrada entao Soma */
update produto
set
pro_saldo_fis2 = pro_saldo_fis2 + i.mov_qt + i.mov_qtvale,
pro_saldo_fin2 = pro_saldo_fin2 + i.mov_mercadoria + i.mov_vlvale from inserted i
where
pro_deposito = i.mov_deposito and
pro_codigo = i.mov_codigo
else /* entao Subtrai se for Saida */
update produto
set
pro_saldo_fis2 = pro_saldo_fis2 - i.mov_qt - i.mov_qtvale,
pro_saldo_fin2 = pro_saldo_fin2 - i.mov_mercadoria - i.mov_vlvale from inserted i
where
pro_deposito = i.mov_deposito and
pro_codigo = i.mov_codigo;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 13:12:48

create trigger u_tblPrice
on dbo.tblPrice
for update
as
begin
if Updated(Price)
if (select count(*) from inserted) > 1
rollback transaction
end

HTH

=================================================================

The best things in life are nearest: Breath in your nostrils, light in your eyes, flowers at your feet, duties at your hand, the path of right just before you. -Robert Louis Stevenson, novelist, essayist, and poet
(1850-1894)

ghaza
Starting Member

3 Posts

Posted - 2004-12-22 : 07:32:49

Thanks a lot for your replies - I have the following comments:

AndyB13: I can use stored procedures to update the data - an application is already written that does this and it won't be changed to using stored procedures :-( The application uses SQL statements to update the data like the examples I have given.
"update tblprice set pricephoto = 35" does indeed update every row in the table but I want this to be allowed - the "trigger" should only "block" the data if the field Price is updated by the update statement.

CLages: Your example might work but I am afraid I don't understand it completely

Bustaz Kool: You nailed it on the spot except the "if Updated(Price)" line returns the error that the Updated function is not recognized. If I remove that line the trigger works somewhat since it will only update the data if it would affect only one row. Only problem is that this still dosn't work "update tblprice set pricephoto = 35" as I would like it to. Is Updated() one of your own function or maybe something included in a newer service pack than I have?

Basicly the thing I need is a way of preventing accidental updates to the Price coloumn that would update its value to the same for all rows in the table but if someone wanted to update severel rows with the same value for "PricePhoto" that should be allowed.

Another question - there is already a trigger on the table that affects the price field. What the trigger does is that it logs if a user changes its value - how do I make this trigger fire only after this new trigger and not before?

Thanks again

Peter

ghaza
Starting Member

3 Posts

Posted - 2004-12-22 : 07:57:57

Ok - forget my last post, seems the function was called Update() and not Updated() - it works perfectly now except for the question of:

There is already a trigger on the table that affects the price field. What the trigger does is that it logs if a user changes its value - how do I make this trigger fire only after this new trigger and not before?

Thanks again,

Peter

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-22 : 09:43:47

All of the triggers and there actions are considered to be part of the original event. They are "atomic" in the sense that either everything succeeds or everything rolls back. You can't execute one without the other.

You can control the order that they fire a bit in SQL 2000. Look up " CREATE TRIGGER" in BOL; specifically the portion that talks about sp_settriggerorder.

HTH

=================================================================

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources