SQL Server - after insert trigger - update another

Question

I've got this database trigger:

CREATE TRIGGER setDescToUpper
ON part_numbers
 AFTER INSERT,UPDATE
AS
DECLARE @PnumPkid int, @PDesc nvarchar(128)

SET @PnumPkid = (SELECT pnum_pkid FROM inserted)
SET @PDesc = (SELECT UPPER(part_description) FROM inserted)

UPDATE part_numbers set part_description_upper = @PDesc WHERE pnum_pkid=@PnumPkid

GO

Is this a bad idea? That is to update a column on the same table. I want it to fire for both insert and update.

It works, I'm just afraid of a cyclical situation. The update, inside the trigger, fires the trigger, and again and again. Will that happen?

Please, don't nitpick at the upper case thing. Crazy situation.

Answer 1

create or replace 
TRIGGER triggername BEFORE INSERT  ON 
table FOR EACH ROW 
BEGIN
/*
Write any select condition if you want to get the data from other tables
*/
:NEW.COLUMNA:= UPPER(COLUMNA); 
--:NEW.COUMNa:= NULL;
END; 

The above trigger will update the column value before inserting. For example if we give the value of COLUMNA as null it will update the column as null for each insert statement.