November 1, 2004 at 11:52 am
I am having trouble setting up a trigger to check the units of a number field.
I have a table with a float field called length. The trouble is some people are entering a length in meters, centimeters, and millimeters. I am trying to code a trigger that will fire on update or insert that will check the value of length and if it is greater than 20 chances are the number is in millimeters and I want to divide by 10 before the insert or update is commited to the table. If the number is less than 0 then the number is in meters and I want to multiply by 100 before commiting it to the table. I do not understand about capturing the value in a variable and how to code this. I am pasting my idea so far. Any help on correct syntax would be appreciated.
Thanks very much for any help
CREATE TRIGGER [Set_Centimeters] ON [dbo].[SAMPLE_DETAIL]
FOR INSERT, UPDATE
AS
If @length > 10
Begin
@length = @length/10
End
If @length < 0
Begin
@length = @length*100
End
Go
November 1, 2004 at 3:58 pm
I think something like this should work. You need to read BOL for more info on how triggers work, but this one uses a notional recordset called 'inserted', which can be thought of as a copy of all of the records that have been inserted or deleted as part of a single batch.
The trigger updates the sample_detail table, but the inner join on 'inserted' ensures that only the inserted/updated records can possibly be affected by the update and the WHERE clause narrows these records down further, to just those that you wish to update.
I haven't tested this, so there may be minor syntax errors. Good luck.
create trigger Set_Centimeters ON [dbo].[SAMPLE_DETAIL] for insert, update as
update s
set length =
(case
when s.length > 10 then s.length / 10.0
when s.length 10 or i.length < 0
go
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2004 at 6:10 pm
Joe
I cannot see anywhere in the original post any mention of files.
As for columns and rows not being referred to as fields and records, please explain what practical problems this creates.
While I agree in general with your advice, Robbs is obviously a novice so I think your message is a bit harsh.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2004 at 8:48 am
Thanks for the help and comments. I do make use of constraints but I did not think constraints can change data values and from my reading BOL that is where a trigger can help. When I have a database application I check and change values where appropriate within the application before sending the data to the database, but for this I wanted to do this with a procedure within the database itself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply