Trigger

realgone_ · January 27th, 2005, 10:36 AM

I need to create an insert trigger. My situation is this:
Field A is populated with value '123456' at this point the trigger responds to this insert by executing an algorithm that converts '123456' to 'ABCDEF' where the alpha letter coresponds to the numeric order. I then need this trigger to populate Field B with 'ABCDEF'.

Any help would be appreciated.

jemacc · January 27th, 2005, 11:01 AM

Do you already have the algorithm to change the column from '123456' to a string
Look at this example:

Code:

create trigger as update_insert_b
on <your table>
for insert
as
if update(col_A)
begin
update <your table>
set col_B=col_A
from <your Table> A
join inserted i
on A.id=i.id
where A.col_A='Your Value'-- our what ever you decide
end

Jaime E. Maccou

realgone_ · January 27th, 2005, 11:10 AM

Jaime,
The algorithm is what I need help with.
I suspect that I would need to use CASE in this situation?

Select Col_A,
Case Col_A
When '123456' then 'ABCDEF'
End as Col_B

Something along these lines but I am having trouble conceptualizing.

jemacc · January 27th, 2005, 11:33 AM

I have never used it before but books online has an example that may be helpful.

Examples
A. Use ASCII and CHAR to print ASCII values from a string
This example prints the ASCII value and character for each character in the string New Moon.

SET TEXTSIZE 0
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1
   END
GO

Jaime E. Maccou

realgone_ · January 27th, 2005, 11:49 AM

Thanks let me study this.