Is there a way check deleted.val > inserted.val? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is there a way check deleted.val > inserted.val?

Author

Topic

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-05 : 03:24:19

Good morning guys!
I am working on an instead of trigger and I need to know if deleted.val is bigger than inserted.val, and have two different updates, one for each case. Here is the case:

USE db_name
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'update_val2' AND type = 'TR')
DROP TRIGGER update_val2
GO
create trigger update_val2
on dbo.table_name
instead of update
as
begin
update table_name
set val = rtrim(cast(cast(
cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
- 1.15 * (cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
- cast(rtrim(substring(inserted.val,1,7)) as decimal(9,2)))
as decimal(9,2)) as varchar))
from table_name
join inserted
on inserted.tac_key = table_name.tac_key
end

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 03:40:13

update table_name
set val = rtrim(cast(cast(
cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
- 1.15 * (cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
- cast(rtrim(substring(inserted.val,1,7)) as decimal(9,2)))
as decimal(9,2)) as varchar))
from table_name
join inserted
on inserted.tac_key = table_name.tac_key
and convert(decimal(9,2),convert(varchar(4,inserted.val) > convert(decimal(9,2),convert(varchar(7),table_name.val))

and another update for#
and convert(decimal(9,2),convert(varchar(4,inserted.val) <= convert(decimal(9,2),convert(varchar(7),table_name.val))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-05 : 03:46:34

Could you give an example nr? Can I have two updates like this in a trigger:

update table_name
...
where ...
update table_name
...
where ...

I tried that, but the second update was never executed.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 04:23:37

Better be able to as you have to repeat the update which fires the instead of trigger.
A simple test is always a good idea.

create table t (s varchar(10), i int, j int)

insert t select 'a',1, 0
insert t select 'b',100,0

create trigger tr on t instead of update
as

update t set j = i.i+1 from t join inserted i on t.s = i.s where t.i < 50
update t set j = i.i+20 from t join inserted i on t.s = i.s where t.i >= 50

update t set i = i.i from t join inserted i on t.s = i.s
go

update t set i = i+1
select * from t
s i j
---------- ----------- -----------
a 2 3
b 101 121

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 04:25:54

The other option wit hthe case statement is

update t set j = case when t.i < 50 then i.i+1 else i.i+20 end from t join inserted i on t.s = i.s
But this can make the query more complicated.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-05 : 05:13:53

I'll try it nr. One last question though (I hope): What do you mean by i.i+1? Is this another way to increase the value of i?

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-05 : 05:14:47

Oh, I forgot to thank you. Thank you very much!

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-05 : 06:49:41

My last question is stupid. Forget it. i is the alias for inserted table!

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-06 : 02:40:49

Unfortunately, it does not work (yet, I hope). I tried both the one with the two update clauses and the other with case. I wanted the second case to be something like j = inserted.j (the usual update), when inserted value (new) is bigger than the old value. The result was that only the second one was working in this case.
The first one, if alone, works!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-06 : 02:45:34

Should work. Suspect there is somethng wrong with your code.
Remember that the first update will change the value on the table so the second will be working on the updated row.

It will action both updates - you just have to make sure that the second does not update rows already updated by the first and vice-versa.
If it's not possible t do this then use a single update statement and use a case statement to set the values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-06 : 03:04:42

No, I just want to execute two different updates, one to be executed if, and only if, the old value (table value) is bigger that the new value (inserted table value) and the other to be executed only if the new value is bigger.
I do not want two concurrent updates...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-06 : 04:18:27

so
update tbl set ....
from tbl
join inserted i
on i.PK = tbl.PK
where i.val > t.val

update tbl set ....
from tbl
join inserted i
on i.PK = tbl.PK
where i.val <= t.val

If it doesn't execute both updates (updating different rows in each) then there is something wrong with your code.
You're in trouble if you can't do two updates in the trigger because you have to repeat the firing update at the end.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-06 : 07:16:35

I do not know if there's something wrong with my code, I do not get an error message. But, when I try the trigger with two updates, I go in the enterprise manager and change values there, and I notice that only the second of the two (updates) is executed. In fact I update one value at a time, and the result is that SQL Server ignores my where clauses! When I have only one update in the trigger, it works fine (without where clause). Is there any problem with the inserted table in the where clause? Have you tried, the example that you've given me?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-06 : 13:11:19

>> I notice that only the second of the two (updates) is executed
Doubt it unless you have control of flow sattements around it. It could be that the first statement is not updating any rows or the second is cancelling out the first update.

>> and the result is that SQL Server ignores my where clauses
Again doubtful.

Try it in query analyser and you will see the record counts for each update executed.
You can also put a select after each update in the trigger to see the affect.

Enterprise manager is not very good for data updates (or much else really).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-07 : 03:13:39

Good Morning! Well, this problem had to do with SQL Server (Server's option) about nested queries. I hadn't checked to allow nested queries in Server's properties.
I have another problem though. I remind you that I use an 'instead of trigger' and an 'after update' trigger (to check if the new values are within a range, and if they are, set their new values accordingly).
The problem is that one of these triggers (I suspect it is the 'after update' one) multiplies the affected rows. When one row is updated, I get a 'copy' of this row with somewhat close (or equal) value (like this row was inserted, even if I'm not telling SQL Server to insert anything!).
Dear friend nr(or anyone else), could you help me in this case? What is causing the 'multiplication' of rows?
Thanks in advance! I feel very grateful for your help already!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 04:38:03

Can you post what you have.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-07 : 04:54:09

USE db_name
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'update_val2' AND type = 'TR')
DROP TRIGGER update_val2
GO
create trigger update_val2
on dbo.table_name
instead of update
as
update table_name
set val = rtrim(cast(cast(
cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
- 1.15 * (cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
- cast(rtrim(substring(inserted.val,1,7)) as decimal(9,2)))
as decimal(9,2)) as varchar))
from table_name
join inserted
on inserted.pk = table_name.pk
WHERE cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
> cast(rtrim(substring(inserted.val,1,7)) as decimal(9,2))

update table_name
set val = inserted.val
from table_name
join inserted
on inserted.pk = table_name.pk
WHERE cast(rtrim(substring(table_name.val,1,7)) as decimal(9,2))
< = cast(rtrim(substring(inserted.val,1,7)) as decimal(9,2))
go

USE db_name
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'update_val' AND type = 'TR')
DROP TRIGGER update_val
GO
create trigger update_val
on dbo.table_name
after update
as
begin
update table_name
set val = '0.00'
from table_name
join inserted
on inserted.pk = table_name.pk
where SUBSTRING(table_name.val, 1, 4 ) between '0.01' and '0.28'
or SUBSTRING(table_name.val, 1, 1) = '-'
end

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-27 : 11:18:24

At last I solved it guys! I added both triggers on the instead of trigger (with case statements).

Thanks very much for your help nr! You gave me the original idea!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources