随笔- 19  评论- 0  文章- 0 

数据库当中处触发器的应用

1.先创建一个test 和一个test2  两个表

USE [WZDb]
GO

/****** Object:  Table [dbo].[test]    Script Date: 05/13/2013 14:21:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [content] [varchar](500) NOT NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

USE [WZDb]
GO

/****** Object:  Table [dbo].[test2]    Script Date: 05/13/2013 14:21:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test2](
 [id] [uniqueidentifier] NOT NULL,
 [name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[test2] ADD  CONSTRAINT [DF_test2_id]  DEFAULT (newid()) FOR [id]
GO

 2.先看一下添加的触发器。当给test2 添加一条数据 相对应的给test1 也添加一条

    USE [WZDb]
GO
/****** Object:  Trigger [dbo].[trig_test2]    Script Date: 05/13/2013 14:22:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=====给Test2添加数据的时候也改test1添加====
ALTER trigger  [dbo].[trig_test2]
    on [dbo].[test2]   for  insert
      as
         declare  @name  varchar(50)
        -- declare  @name2  varchar(50)
         select  @name=name  from inserted
        
          INSERT INTO [WZDb].[dbo].[test]
           ([content])
     VALUES
           (@name)
           print '开始输出'
           print @name
           print '输出完毕'

 

2.删除数据的触发器

  

USE [WZDb]
GO
/****** Object:  Trigger [dbo].[trrig_del]    Script Date: 05/13/2013 14:23:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trrig_del]
  on  [dbo].[test]  for delete
AS
   declare  @name varchar(50)
   select @name=content from  deleted
   if  exists(select * from test2  where name=@name)
   begin
    delete  from   test2  where  name=@name
     print 'ddd'
    end

3.更新触发器

   还没有完全理解,后续更新

  

posted on 2013-05-13 14:36  AStronghcm  阅读(...)  评论(...编辑  收藏