sqlserver- 触发器

目录

• 测试数据生成
• 约束- 增加/修改
• 触发器TRIGGER

1. after (for)
2. instead of
3. 触发事件查询

• 拓展- RAISERROR
• 拓展- sys.开头表单详细描述

---

-- 注1：在表survey中增加自增主键Id列
-- 注2：增加辅表question_log
-- 注3：增加辅表test_insert
-- 测试数据
USE master
GO

IF EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE name='LeetCode')
	BEGIN
		DROP DATABASE LeetCode
		SELECT 1
	END
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO

-- 新建数据表
-- 注意约束
-- IF EXISTS(SELECT * FROM sysobjects WHERE name='survey_log')
IF (OBJECT_ID('survey_log', 'U') IS NOT Null)
	DROP TABLE survey_log
GO
CREATE TABLE survey_log
(
	Id INT IDENTITY
		CONSTRAINT PK_surveyLog PRIMARY KEY,
	uid INT,
	action VARCHAR(20) 
		CONSTRAINT Check_action 
		--CHECK(action IN ('show', 'skip', 'answer')),
		CHECK(action='show' or action='answer' or action='skip'),
	--action VARCHAR(20),
	--CONSTRAINT Check_action CHECK(action in ('show', 'answer', 'skip')),
	question_id INT,
	answer_id INT,
	q_num INT,
	timestamp INT,
	date DATETIME 
		CONSTRAINT Default_date DEFAULT GETDATE()
)
GO

-- 查询 自定义对象
SELECT *
FROM sysobjects
WHERE xtype NOT IN ('S', 'IT', 'SQ')

-- 查询 表数据
SELECT *
FROM survey_log

-- 查询 表信息
EXEC sp_help survey_log