quote:

---

Alter TRIGGER [dbo].[proc_trgr_Catalog] ON [dbo].[tblPart]
FOR INSERT, UPDATE
AS

DECLARE
@Idx INT,
@Idx2 INT,
@Upd INT,
@Limit INT,
@Ordinal INT,
@CatNo varchar(20),
@Column varBinary(100),
@ValueBefore VARCHAR(800),
@ValueAfter VARCHAR(800),
@Byte Bigint,
@Field VARCHAR(200),
@x VARCHAR(50),
@Src_Module VARCHAR(50),
@For_Whom VARCHAR(50),
@Dest_Email_Body VARCHAR(8000),
@Dest_Email_Subj VARCHAR(255)

SET @Dest_Email_Body = ''

IF EXISTS(SELECT * from Inserted where Technique_ID = 'G')
BEGIN
IF UPDATE(Cost) OR UPDATE(Status) OR UPDATE(US_List) OR UPDATE(PHE_Part_No) OR UPDATE(Lead_Time_Days) OR UPDATE(Description_1) OR UPDATE(Description_2)
BEGIN

SELECT @Limit = COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblPart'

SET @Idx = 1
SET @Byte = 1
SET @ValueBefore = (SELECT CATALOG_NO FROM Deleted)
SET @ValueAfter = (SELECT CATALOG_NO FROM Inserted)
SET @Dest_Email_Body = 'New catalog number ' + @ValueAfter + ' has been added.'
SET @Column = COLUMNS_UPDATED()
IF @ValueBefore = @ValueAfter
BEGIN
SET @Dest_Email_Body = ''
WHILE @Idx < @Limit + 1
BEGIN
SET @Upd = CONVERT(INT, SUBSTRING(@Column,@Idx,1))
IF @Upd > 0
BEGIN
SET @Ordinal = (@Idx - 1) * 8 + @Upd
SELECT @Field = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblPart' AND ORDINAL_POSITION = @Ordinal
SET @Dest_Email_Body = @Dest_Email_Body + ' The value for the field(s), ' + @Field + ', for Catalog Number ' + @ValueBefore + ' Was Updated. '
SET @Field = ''
END
SET @Idx = @Idx + 1
END
END
BEGIN
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail
@recipients='group@email.xxx'
,@message=@Dest_Email_Body
,@subject='Changes have been made to the Part Catalog'
EXEC master.dbo.xp_stopmail
END
END
END

---