select
case when [text] like '%[ '+char(10)+']for[ '+char(13)+']%'
then
substring(text,
patindex('%[ '+char(10)+']for[ '+char(13)+']%',text)+5,
patindex('%[ '+char(10)+']as[ '+char(13)+']%',text)-patindex('%[ '+char(10)+']for[ '+char(13)+']%',text)-5)
else substring(text,
charindex('INSTEAD OF',text),
patindex('%[ '+char(10)+']as[ '+char(13)+']%',text)-charindex('INSTEAD OF',text))
end 类型
from syscomments c,sysobjects o where o.id=c.id and o.xtype='TR'
Select text from syscomments c
join sysobjects o on c.id = o.id
Join sysobjects s on o.parent_obj = s.id
where 1=1
and o.xtype = 'TR'
and s.name = '表名'
select substring(text,charindex('for ',text)+4,charindex(' as',text)-charindex('for ',text)-3) ,* from syscomments c,sysobjects o where o.id=c.id and o.xtype='TR'
@trigtype=(SELECT CASE WHEN dbo.sysobjects.deltrig > 0 THEN 'DELETE'
WHEN dbo.sysobjects.instrig > 0 THEN 'INSERT'
WHEN dbo.sysobjects.updtrig > 0 THEN 'UPDATE' END
FROM dbo.sysobjects INNER JOIN
dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @ObjectName))
END
要知道触发器是否enable,必须根据不同类型进行判断!
SELECT NAME,CASE WHEN STATUS & 0X2000<>0 THEN 'Y' ELSE NULL END AS [INSTEAD OF],
CASE WHEN STATUS & 0X100<>0 THEN 'Y' ELSE NULL END AS [DELETE],
CASE WHEN STATUS & 0X200<>0 THEN 'Y' ELSE NULL END AS [UPDATE],
CASE WHEN STATUS & 0X400<>0 THEN 'Y' ELSE NULL END AS [INSERT],
CASE WHEN STATUS & 0X900<>0X900 AND STATUS & 0XA00<>0XA00 AND STATUS & 0XC00<>0XC00 THEN 'Y' ELSE 'N' END AS [ENABLED]
FROM SYSOBJECTS
WHERE TYPE='TR'