INSERT UPDATE Trigger Question

[imani_technology]

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.

[John Bell]

Hi

Try something like:

CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )

CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END

INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')

SELECT * from Test
John

<im**************@yahoo.com> wrote in message
news:f9**************************@posting.google.c om...

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.

[Simon Hayes]

im**************@yahoo.com wrote in message news:<f9**************************@posting.google. com>...

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.

You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):

create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn

Simon