触发器，，，关于text、ntext、image插入问题

来源：百度知道 编辑：UC知道 时间：2024/05/30 18:07:12
触发器：
CREATE TRIGGER deletedBaseproperty ON LD_PE_BASEPROPERTY
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into LD_PE_BASEPROPERTY_DELETED(peCode,name,sex,photo,pID,married,nation,politic,born,origin,address,postCode,conTel,highEdu,graSchool,profession,perPost,stuExper)
SELECT * FROM DELETED --该句执行报错

END
GO
其中stuExper,jobExper,familyMem和photo分别为text和image类型

执行报错：
不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。
请问：如何解决这种问题？
使用instead of可以直接取值插入LD_PE_BASEPROPERTY_DELETED
但原始表应该不执行SQL语句，所以无法删除记录！咋办？

CREATE TRIGGER deletedBaseproperty ON LD_PE_BASEPROPERTY
INSTEAD OF DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into LD_PE_BASEPROPERTY_DELETED(peCode,name,sex,photo,pID,married,nation,politic,born,origin,address,postCode,conTel,highEdu,graSchool,profession,perPost,stuExper)
SELECT * FROM DELETED --该句执行报错

--再删除一次
delete LD_PE_BASEPROPERTY where 表的主键 in (select 表的主键 from DELETED)

END
GO

这个是触发器中inserted和deleted表的限制，所以我觉得换个思路做
1、把触发器定为 INSTEAD OF类型触发器
2、触发器器里所做的处理为，获得要被删除的信息的主键列的值
3、然后从LD_PE_BASEPROPERTY原始表中根据第2步获得的主键列的值把所有信息取出来插入到LD_PE_BASEPROPERTY_DELETED表中。