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


    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.更新触发器

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

      

  • 相关阅读:
    把信贷风险管理浓缩为50个要点(赶紧收藏吧)!
    AjaxControlToolkit没有通过WebResource.axd加载css导致ajaxToolkit:TabPanel无法显示正确的样式
    启动vmware中的虚拟机的时候,提示Failed to lock the file
    linq to xml There are multiple root elements.
    How to create a List of ValueTuple?
    SET IDENTITY_INSERT 和 DBCC CHECKIDENT
    NOT IN clause and NULL values
    ASP.NET Session and Forms Authentication and Session Fixation
    asp.net下的cookieName
    flywaydb and sql server
  • 原文地址:https://www.cnblogs.com/hechunming/p/3075725.html
Copyright © 2020-2023  润新知