sqlserver触发器

 
--测试数据，首先我们创建测试数据，共有两张表：课程表（course）与学生表（student），其中学生表中
--的course_id字段为course的外键
 
-- USE [demo]
-- 创建course表
CREATE TABLE course(
    course_id varchar(50) PRIMARY KEY,
    course_name varchar(50)
)
GO
-- 创建student表
CREATE TABLE student(
    student_id varchar(50) PRIMARY KEY,
    student_name varchar(50),
    course_id varchar(50) FOREIGN KEY REFERENCES course(course_id)
)
GO
-- 插入数据
INSERT INTO course (course_id,course_name)
VALUES 
('C001','语文'),
('C002','数学'),
('C003','英语')
GO
 
INSERT INTO student (student_id,student_name,course_id)
VALUES
('S001','Lucy','C001'),
('S002','Jack','C002'),
('S003','Jane','C003'),
('S004','Jameson','C001')
GO

--insert触发器示例
--需求，我们需要在course表中添加更新时间字段（update_time），当我们插入学生的时候，就要对相应的该---课程的时间进行更新，代码如下：
-- 为course表添加字段
--ALTER TABLE course
--ADD update_time varchar(50)
--GO
-- 创建student表的触发器
IF (object_id('student_insert','tr') is not null)
   drop TRIGGER student_insert
GO
CREATE TRIGGER [dbo].[student_insert] ON [dbo].[student]
FOR insert
AS
DECLARE @c_name varchar(255), @cc VARCHAR(255), @course_id VARCHAR(50);
SELECT @course_id = course_id FROM inserted;
SELECT @c_name = (SELECT course_name FROM course WHERE course_id = @course_id);
SELECT @cc = (SELECT cc FROM course WHERE course_id = @course_id);
UPDATE student SET c_name = @c_name WHERE course_id = @course_id;
UPDATE student SET cc = @cc WHERE course_id = @course_id;
PRINT '相应课学生表已更新'
 

IF (object_id('user_insert','tr') is not null)
   drop TRIGGER user_insert
GO
CREATE TRIGGER [yjj].[user_insert] ON [yjj].[JZT_USER_BASE_INFO]
FOR insert
AS
DECLARE @B2B_ACCOUNTS VARCHAR(100), @druglic varchar(255), @drupdate VARCHAR(50), 
@cantsale VARCHAR(255), @license_invalidate VARCHAR(50);
SELECT @B2B_ACCOUNTS = B2B_ACCOUNTS FROM inserted;
SELECT @druglic = (SELECT LICENSE_NO FROM JZT_USER_LICENSE_INFO WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS AND  LICENCE_NAME = '药品经营许可证');
SELECT @drupdate = (SELECT LICENSE_VALIDITY_END FROM JZT_USER_LICENSE_INFO WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS AND LICENCE_NAME = '药品经营许可证');
SELECT @cantsale = (SELECT LICENSE_NO FROM JZT_USER_LICENSE_INFO WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS AND LICENCE_NAME = '营业执照');
SELECT @license_invalidate = (SELECT LICENSE_VALIDITY_END FROM JZT_USER_LICENSE_INFO WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS AND LICENCE_NAME = '营业执照');
UPDATE JZT_USER_BASE_INFO SET druglic = @druglic WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS;
UPDATE JZT_USER_BASE_INFO SET drupdate = @drupdate WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS;
UPDATE JZT_USER_BASE_INFO SET cantsale = @cantsale WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS;
UPDATE JZT_USER_BASE_INFO SET license_invalidate = @license_invalidate WHERE B2B_ACCOUNTS = @B2B_ACCOUNTS;
PRINT '会员基础表已更新'