[sqr-users] SQL Server 7 Trigger

[DKubicek@grede.com]

Maximo 4.1.1P03 Sql Server7

I am trying to create the following trigger to protect the short
description field on the item table. This is being done because my users
still need access to the longdescription field so I can not make the field
read only in Object Nationaliser. the problem is in the line:
   select @old_desc         = (select a.description from maxtest.dbo.item
a, inserted ins where a.itemnum = ins.itemnum)
This does not set @old_desc = to the description befor it is changed.
if I put
   select @old_desc         = 'OLD DESCRIPTION'
it does replace the changed description with "OLD DESCRIPTION'. Does
anybody have any idea why this trigger would not work.


IF EXISTS (SELECT name
         FROM   sysobjects
         WHERE  name = 'Protect_Item_Description'
            AND type = 'TR')
    DROP TRIGGER Protect_Item_Description
GO

create trigger Protect_Item_Description on item for update
 as

declare @itemnum            varchar(32)
declare @old_desc           varchar(254)
declare @new_desc           varchar(254)

BEGIN
   select @itemnum          = ins.itemnum           from inserted ins
   select @new_desc         = ins.description       from inserted ins
   select @old_desc         = (select a.description from maxtest.dbo.item
a, inserted ins where a.itemnum = ins.itemnum)

    update item set description = @old_desc where itemnum = @itemnum
END

Daniel Kubicek (Maximo SA)
Senior Programmer Analyst - Engineering & Procurement
Grede Foundries, Inc.
414.256.9210  414-708-9210 Cell
dkubicek@grede.com

Daniel Kubicek (Maximo SA)
Senior Programmer Analyst - Engineering & Procurement
Grede Foundries, Inc.
414.256.9210  414-708-9210 Cell
dkubicek@grede.com


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users