某一报表,需要用户自己设定某一项目所显示字段不一样。因此需要设计一个表来管理这些字段。
但为了直接显示到目标表上,触发器可以轻松解决此问题。
下面一个插入触发器一个删除触发器,请参考SQL Server代码:
代码
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [ dbo ] . [ tri_CargoSpace_Insert ] ON [ dbo ] . [ CargoSpace ]
FOR INSERT -- 插入触发器
AS
BEGIN
SET NOCOUNT ON
DECLARE @CargoSpaceId NVARCHAR ( 50 )
-- 从触发器虚拟表INSERTED取得值
SELECT @CargoSpaceId = [ CargoSpaceId ] FROM INSERTED
-- 判断目标表字段是否存在,如果不存在,修改目标表,添加字段。
EXECUTE ( ' IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID( '' ItemGeneralStock '' ) AND [name] = ''' + @CargoSpaceId + ''' )
ALTER TABLE [ItemGeneralStock] ADD [ ' + @CargoSpaceId + ' ] DECIMAL(18,6) NULL ' )
END
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [ dbo ] . [ tri_CargoSpace_Insert ] ON [ dbo ] . [ CargoSpace ]
FOR INSERT -- 插入触发器
AS
BEGIN
SET NOCOUNT ON
DECLARE @CargoSpaceId NVARCHAR ( 50 )
-- 从触发器虚拟表INSERTED取得值
SELECT @CargoSpaceId = [ CargoSpaceId ] FROM INSERTED
-- 判断目标表字段是否存在,如果不存在,修改目标表,添加字段。
EXECUTE ( ' IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID( '' ItemGeneralStock '' ) AND [name] = ''' + @CargoSpaceId + ''' )
ALTER TABLE [ItemGeneralStock] ADD [ ' + @CargoSpaceId + ' ] DECIMAL(18,6) NULL ' )
END
代码
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [ dbo ] . [ tri_CargoSpace_Delete ] ON [ dbo ] . [ CargoSpace ]
FOR DELETE -- 删除触发器
AS
BEGIN
SET NOCOUNT ON
-- 宣告一个变量
DECLARE @CargoSpaceId NVARCHAR ( 50 )
-- 从触发器虚拟表DELETED取得值
SELECT @CargoSpaceId = [ CargoSpaceId ] FROM DELETED
-- 判断目标表字段是否存在,如果存在,修改目标表,删除字段。
EXECUTE ( ' IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID( '' ItemGeneralStock '' ) AND [name] = ''' + @CargoSpaceId + ''' )
ALTER TABLE [ItemGeneralStock] DROP COLUMN [ ' + @CargoSpaceId + ' ] ' )
END
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [ dbo ] . [ tri_CargoSpace_Delete ] ON [ dbo ] . [ CargoSpace ]
FOR DELETE -- 删除触发器
AS
BEGIN
SET NOCOUNT ON
-- 宣告一个变量
DECLARE @CargoSpaceId NVARCHAR ( 50 )
-- 从触发器虚拟表DELETED取得值
SELECT @CargoSpaceId = [ CargoSpaceId ] FROM DELETED
-- 判断目标表字段是否存在,如果存在,修改目标表,删除字段。
EXECUTE ( ' IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID( '' ItemGeneralStock '' ) AND [name] = ''' + @CargoSpaceId + ''' )
ALTER TABLE [ItemGeneralStock] DROP COLUMN [ ' + @CargoSpaceId + ' ] ' )
END