触发器

/*--------------使用数据库--------------------------*/
USE trigger 
GO 
/*--------------------------------------------------*/






/*---------------------------------是否存在卷烟库存表、卷烟销售表若存在删除--------------------------*/


IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷烟销售表') 
DROP TABLE 卷烟销售表 
GO 


/*-----------------------------------------------------------------------------------------------------*/












/*--------------创建卷烟销售表 --------------------------*/
CREATE TABLE 卷烟销售表 
( 
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 
购货商 VARCHAR(40) NULL, 
销售数量 INT NULL, 
销售单价 MONEY NULL, 
销售金额 MONEY NULL 
) 
GO 
/*---------------------------------------------------------*/










/*--------------创建卷烟库存表  --------------------------*/
CREATE TABLE 卷烟库存表 
( 
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 
库存数量 INT NULL, 
库存单价 MONEY NULL, 
库存金额 MONEY NULL 
) 
GO 
/*---------------------------------------------------------*/
















/*------------------------------------创建是否卷烟库存表INSERT触发器-------------------------------------*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟库存表') 
DROP TRIGGER T_INSERT_卷烟库存表 
GO 
/*--------------------------------------------------------------------------------------------------------*/








/*-------------------------创建卷烟库存表INSERT触发器--------------------------*/
CREATE TRIGGER T_INSERT_卷烟库存表 
ON 卷烟库存表 
FOR INSERT 
AS 
--提交事务处理 
BEGIN TRANSACTION 
--强制执行下列语句，保证业务规则 
UPDATE 卷烟库存表 
SET 库存金额 = 库存数量 * 库存单价 
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED) 
COMMIT TRANSACTION
GO 
/*--------------------------------------------------------------------------------*/






/*------------------------------------------插入卷烟库存信息------------------------------------------------------*/
INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额) 
SELECT '红塔山新势力',100,12,1200 UNION ALL 
SELECT '红塔山人为峰',100,22,NULL UNION ALL 
SELECT '云南映像',100,60,500 UNION ALL 
SELECT '玉溪',0,30,0 
GO 
/*--------------------------------------------------------------------------------------------------------*/










/*-----------------------------------------查询卷烟库存信息------------------------------------------------*/
SELECT * FROM 卷烟库存表 
GO 
/*--------------------------------------------------------------------------------------------------------*/










IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟销售表') 
DROP TRIGGER T_INSERT_卷烟销售表 
GO 


CREATE TRIGGER T_INSERT_卷烟销售表 
ON 卷烟销售表 
FOR INSERT 
AS 
BEGIN TRANSACTION 
--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零 
IF NOT EXISTS ( 
SELECT 库存数量 
FROM 卷烟库存表 
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) 
) 
BEGIN 
--返回错误提示 
RAISERROR('错误！该卷烟不存在库存，不能销售。',16,1) 
--回滚事务 
ROLLBACK 
RETURN 
END 


IF EXISTS ( 
SELECT 库存数量 
FROM 卷烟库存表 
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 
库存数量 <= 0 
) 
BEGIN 
--返回错误提示 
RAISERROR('错误！该卷烟库存小于等于0，不能销售。',16,1) 
--回滚事务 
ROLLBACK 
RETURN 
END 


--对合法的数据进行处理 


--强制执行下列语句，保证业务规则 
UPDATE 卷烟销售表 
SET 销售金额 = 销售数量 * 销售单价 
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) 


DECLARE @卷烟品牌 VARCHAR(40) 
SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED) 


DECLARE @销售数量 MONEY 
SET @销售数量 = (SELECT 销售数量 FROM INSERTED) 


UPDATE 卷烟库存表 
SET 库存数量 = 库存数量 - @销售数量, 
库存金额 = (库存数量 - @销售数量)*库存单价 
WHERE 卷烟品牌 = @卷烟品牌 
COMMIT TRANSACTION 
GO 










/*--------------------------------------------------------------------------------
--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。 
--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。
--------------------------------------------------------------------------------*/  
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
SELECT '红塔山新势力','某购货商',10,12,1200 
GO 






/*--------------------------------------------------------------------------------
--针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。 
--触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。 
--------------------------------------------------------------------------------*/ 
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
SELECT '红塔山人为峰','某购货商',10,22,2000 
GO 








/*--------------------------------------------------------------------------------
针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。 
触发器将报错。
--------------------------------------------------------------------------------*/ 






INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
SELECT '红河V8','某购货商',10,60,600 
GO 






/*--------------------------------------------------------------------------------*/








/*--------------------------------------------------------------------------------
针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为0。 
触发器将报错。 
--------------------------------------------------------------------------------*/






INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
SELECT '玉溪','某购货商',10,30,300 
GO 






/*--------------------------------------------------------------------------------*/








/*----------------------------------------查询卷烟库存表、卷烟销售表信息-----------------------------------------------*/


SELECT * FROM 卷烟库存表 
SELECT * FROM 卷烟销售表 
GO 


/*---------------------------------------------------------------------------------------------------------------------*/






--监测表中的插入，更新，删除的方法，使用触发器实现的
-------------------
--Method 1: TRIGGER
-------------------
--Base Table Definition
IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest
GO
CREATE TABLE CheckSumTest
(
        id                      int                     IDENTITY(1,1) NOT NULL PRIMARY KEY,
        vc1                     varchar(1)      NOT NULL,
        vc2                     varchar(1)      NOT NULL
)
GO
INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b'
INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a'
GO
--Create Audit Summary Table to hold Meta-Data
IF OBJECT_ID('dbo.TableAuditSummary', 'U') IS NOT NULL DROP TABLE dbo.TableAuditSummary
CREATE TABLE dbo.TableAuditSummary
(       id                      INT                     IDENTITY(1,1)   NOT NULL PRIMARY KEY,
        TableName       sysname         NOT NULL,
        LastUpdate      DATETIME        NOT NULL,
        LastExport      DATETIME        NOT NULL
)
GO
INSERT dbo.TableAuditSummary (TableName, LastUpdate, LastExport) VALUES ('dbo.CheckSumTest', GETDATE(), GETDATE())
GO
--Tables that need exporting
SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport
--Create Trigger on all Base Tables
--This fires on any insert/update/delete and writes new LastUpdate column for the table set to Current Date and Time
IF OBJECT_ID('dbo.trg_CheckSumTest_MaintainAuditSummary', 'TR') IS NOT NULL DROP TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary
GO
CREATE TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary
ON dbo.CheckSumTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
        IF (object_id('dbo.CheckSumTest') IS NOT NULL)
                UPDATE dbo.TableAuditSummary SET LastUpdate=GETDATE() WHERE TableName='dbo.CheckSumTest'
END
GO
--Make an Update
UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1
UPDATE dbo.CheckSumTest SET vc1='a', vc2='b' WHERE id=2
--Check Meta-Data
SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport
--When we have Exported the data, we run the following to reset MetaData

UPDATE dbo.TableAuditSummary SET LastExport=GETDATE() WHERE LastUpdate>LastExport

Create Table Student(              --学生表 
        StudentID int primary key,       --学号 
       ) 
Create Table BorrowRecord(                      --学生借书记录表 
        BorrowRecord   int identity(1,1),       --流水号   
        StudentID      int ,                    --学号 
        BorrowDate     datetime,                --借出时间 
        ReturnDAte     Datetime                 --归还时间 
      ) 
--用到的功能有: 
--1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 
--2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 
--等等。 




Create Trigger truStudent  On Student   --在Student表中创建触发器 
for Update                              --为什么事件触发 
As                                      --事件触发后所要做的事情 
   if Update(StudentID)            
      begin 
         Update BorrowRecord 
           Set StudentID=i.StudentID 
           From BorrowRecord br , Deleted   d ,Inserted i     --Deleted和Inserted临时表 
           Where br.StudentID=d.StudentID 
      end      
Create trigger trdStudent On Student for Delete 
As 
Delete BorrowRecord From BorrowRecord br , Deleted d  Where br.StudentID=d.StudentID 




create table ta(number int)
create trigger test_tr on ta
for insert
as
begin
DECLARE @number int,@sql varchar(4000),@sql1 varchar(4000),@i int
DECLARE roy CURSOR
FOR SELECT * from inserted
OPEN roy
FETCH next FROM roy into @number
WHILE @@FETCH_STATUS = 0
begin
begin
  if exists(select 1 from sysobjects where name='tb' and xtype='U')
    begin
         select @i=max(colid)+1 from syscolumns where id=object_id('tb')
         set @sql=N'alter table tb add C'+cast(@i as varchar)+' int null'
         set @sql1=N'update tb set C'+cast(@i as varchar)+'='+cast(@number as varchar)
--print @sql
--print @sql1
         exec(@sql)
         exec(@sql1)
    end
else
    begin
         set @sql=N'create table tb(C1 int)'
         set @sql1=N' insert tb select C1='+cast(@number as varchar)
         exec(@sql)
         exec(@sql1)
    end
end
FETCH NEXT FROM roy INTO @number
end
CLOSE roy
DEALLOCATE roy
end
--










--用触发器实现动态新增列
insert ta 
select 48 union all
select 49
select * from ta
select * from tb




create table ta(id int primary key,name varchar(20))
insert ta
select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 12,'d'
union all select 13,'e'