表与触发器

CREATE TABLE [catalog] (
     [tid] [int] NULL CONSTRAINT [DF_catalog_tid] DEFAULT (1),
     [oid] [int] NULL ,
     [fid] [int] NULL ,
     [lev] [int] NULL CONSTRAINT [DF_catalog_lev] DEFAULT (1),
     [id] [int] NOT NULL ,
     [subn] [int] NULL CONSTRAINT [DF_catalog_subn] DEFAULT (0),
     [sort] [int] NULL CONSTRAINT [DF_catalog_sort] DEFAULT (1),
     [flag] [int] NULL CONSTRAINT [DF_catalog_flag] DEFAULT (1),
     [secid] [int] NULL CONSTRAINT [DF_catalog_secid] DEFAULT (1),
     [desp] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
     [cname] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
     [navstr] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_catalog_navstr] DEFAULT (''),
     [url] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
     [spen] [int] NULL CONSTRAINT [DF_catalog_spen] DEFAULT (0),
     CONSTRAINT [PK_catalog] PRIMARY KEY  CLUSTERED
     (
           [id]
     )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [special] (
     [tid] [int] NULL CONSTRAINT [DF_special_tid] DEFAULT (2),
     [oid] [int] NULL ,
     [fid] [int] NULL ,
     [lev] [int] NULL CONSTRAINT [DF_special_lev] DEFAULT (1),
     [id] [int] NOT NULL ,
     [subn] [int] NULL CONSTRAINT [DF_special_subn] DEFAULT (0),
     [sort] [int] NULL CONSTRAINT [DF_special_sort] DEFAULT (1),
     [flag] [int] NULL CONSTRAINT [DF_special_flag] DEFAULT (1),
     [secid] [int] NULL CONSTRAINT [DF_special_secid] DEFAULT (1),
     [desp] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
     [cname] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
     [navstr] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
     [url] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
     CONSTRAINT [PK_special] PRIMARY KEY  CLUSTERED
     (
           [id]
     )  ON [PRIMARY]
) ON [PRIMARY]
GO

触发器：

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Trigger_catalog_Insert_Update] ON [dbo].[catalog]
FOR INSERT, UPDATE
AS
Begin
     Declare @id int
     Declare @fid int
     Declare @lev int
     Declare @subn int
     Declare @navstr varchar(250)

     select @id=id , @fid=fid , @lev=lev from inserted
    
     select @subn=count(id) from catalog where fid=@fid and lev=@lev and flag>0
     update catalog set subn=@subn where id=@fid

     select @navstr=navstr from catalog where id=@fid
     If @navstr is not null
           update catalog set navstr='' + @navstr + ',' + cast( @id as varchar(20) ) where id=@id
     Else
           update catalog set navstr=cast( @id as varchar(20) ) where id=@id

End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Trigger_catalog_Delete] ON [dbo].[catalog]
FOR Delete
AS
Begin
     Declare @id int
     Declare @fid int
     Declare @lev int
     Declare @subn int
     Declare @navstr varchar(250)

     select @id=id , @fid=fid , @lev=lev from deleted
    
     select @subn=count(id) from catalog where fid=@fid and lev=@lev and flag>0
     update catalog set subn=@subn where id=@fid

End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Trigger_special_Insert_Update] ON [dbo].[special]
FOR INSERT, UPDATE
AS
Begin
     Declare @id int
     Declare @fid int
     Declare @lev int
     Declare @subn int
     Declare @navstr varchar(250)

     select @id=id , @fid=fid , @lev=lev from inserted
    
     select @subn=count(id) from special where fid=@fid and lev=@lev and flag>0
     update special set subn=@subn where id=@fid

     select @navstr=navstr from special where id=@fid
     If @navstr is not null
           update special set navstr='' + @navstr + ',' + cast( @id as varchar(20) ) where id=@id
     Else
           update special set navstr=cast( @id as varchar(20) ) where id=@id

End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Trigger_special_Delete] ON [dbo].[special]
FOR Delete
AS
Begin
     Declare @id int
     Declare @fid int
     Declare @lev int
     Declare @subn int
     Declare @navstr varchar(250)

     select @id=id , @fid=fid , @lev=lev from deleted
    
     select @subn=count(id) from special where fid=@fid and lev=@lev and flag>0
     update special set subn=@subn where id=@fid

End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

上一页  [1] [2] [3] 下一页