Need help with trigger affecting text data type

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)

Need help with trigger affecting text data type

Author

Topic

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-04 : 03:19:04

Good Morning guys! I need to make a trigger to change a value of a text data type column in the database, after each update. Update is being done automatically, e.g. each time when the user makes a phone call, but I need to make a discount to my users. I tried using joins with inserted and deleted tables, in order to get new and old values, and calculate the new output, but I got a message from SQL Server that text, ntext and image data types are not supported in the inserted and deleted tables. I tried conversion, but was not possible to convert text to decimal or numeric. That table has data, I do not want to lose of course. It's just that the database designer (not me) had that stupid idea to make a numeric field a text field!
Thanks in advance!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 03:45:11

Stupid sounds like an apt description.
What is this field really used for?
If it's just for a numeric then change it or add a new field.
If it's used for muliple purposes then split it into multple single purpose columns or put the values on another table.
you can do
convert(numeric,(convert(varchar(20),txtfld))

If you can't do that then to get the value in the column you can join to the table using the PK from inserted. You can convert to varchar and take substrings of the text field

==========================================
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-04 : 03:52:07

This field is used to hold credit amounts, but I can't change it, I tried, but I got the message that text, ntext, etc cannot be converted to numeric or decimal.
I tried substring (without conversion to varchar though), but the message was that text cannot be used in inserted, deleted tables.
Is there any other way to handle text and make calculations using old and new values?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 03:54:55

>> join to the table using the PK from inserted
That will give you the value which you can update.

==========================================
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-04 : 05:31:18

Here is the code guys:

create trigger update_val
on dbo.table_name
after update
as
begin
update table_name
set val = val + 0.15 * (deleted.val - val)
from table_name
join deleted
on deleted.table_name_pk = table_name.table_name_pk
end

I did join the table with inserted. The error is on the line with deleted.val reference (because val is a text datatype field).

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 10:51:51

Bad luck.
The before value is not available to an after trigger.
Have a look at instead of triggers - you can access the text columns from inserted and deleted table in those. But it means you will need to repeat the insert/update/delete within the trigger.

Another option is to have another table into which you place the PK and the new value of the text field in the after trigger at the end. This will then be available to join in the trigger and give the old value of the field.

==========================================
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.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-04 : 13:02:23

What does

SELECT COUNT(*)
FROM Table
WHERE ISNUMERIC(Val)=0

Give you?

I bet not 0

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 13:10:50

Nope - it will give
Argument data type text is invalid for argument 1 of isnumeric function.

==========================================
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.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-04 : 13:21:56

quote:
Originally posted by nr

Nope - it will give
Argument data type text is invalid for argument 1 of isnumeric function.

==========================================
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.

Huh?


USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(1000))
GO

INSERT INTO myTable99(Col1) 
SELECT '1' UNION ALL SELECT '2' UNION ALL
SELECT '3' UNION ALL SELECT '4' UNION ALL
SELECT '5' UNION ALL SELECT '6'
GO
 
SELECT COUNT(*) FROM myTable99 WHERE ISNUMERIC(Col1)=0

INSERT INTO myTable99(Col1) 
SELECT 'a' UNION ALL SELECT 'b' UNION ALL
SELECT 'c' UNION ALL SELECT 'd' UNION ALL
SELECT 'e' UNION ALL SELECT 'f'
GO

SELECT COUNT(*) FROM myTable99 WHERE ISNUMERIC(Col1)=0

DROP TABLE myTable99
GO

My point is, you've already got character data in the column...you have to do something with them....

Or you'll get when Nigel says when you need to do math...

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 16:58:56

Have a look at the question - the problem is that val is text not varchar and you can't do isnumeric or convert numeric without first converting to character.
But the problem PaTRiCKDRD has is nothng to do with that - he can't access the value before the update as text columns aren't available in the inserted and deleted table in after triggers.

==========================================
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.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-04 : 17:14:22

quote:
Originally posted by nr

Have a look at the question - the problem is that val is text not varchar and you can't do isnumeric or convert numeric without first converting to character.
But the problem PaTRiCKDRD has is nothng to do with that - he can't access the value before the update as text columns aren't available in the inserted and deleted table in after triggers.

==========================================
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.

But Of course...I didn't think it was that bad though....

Brett

8-)

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-05 : 03:18:20

My trigger has worked guys!
I have one little final step to accomplish though. Here is the code:

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

My trigger works fine when the credit amount is about to fall. But, when I try to increase the credit amount the same change occurs (I get a 15% raise in this case as well), and I do not want that.

Is there a way to check is the inserted.val is larger that deleted.val?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 03:38:13

The value in table_name will be the old value.
You can put in a case statement which compares the value on table_name (or deleted) and inserted.

==========================================
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:43:48

Can I put a case statement in an update?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources