sqlserver 触发器----记录删除的记录

来源：互联网发布：澳洲保健品知乎编辑：程序博客网时间：2024/06/05 07:12

功能描述：当删除people表的记录时，把删除的记录保存到peopleDelete中

1、创建表

需要删除记录的表people

CREATE TABLE [dbo].[]([ID] [int] IDENTITY(1,1) NOT NULL,[name] [nvarchar](100) NULL,[sex] [int] NULL,primary key(id))

删除记录表

CREATE TABLE [dbo].[peopleDelete]([ID] [int] NOT NULL,[name] [nvarchar](100) NULL,[sex] [int] NULL,primary key(id))

2、在表【people】上创建触发器

USE [wqq]GO/****** Object:  Trigger [dbo].[Insert_PeopleDelete]    Script Date: 07/16/2013 21:27:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================ALTER TRIGGER [dbo].[Insert_PeopleDelete]on [dbo].[people]instead of deleteAS BEGINSET NOCOUNT ON;    insert into peopleDelete select  deleted.id,deleted.name,deleted.sex from deleted    delete from people where ID in(select deleted.id from deleted)END