— Creating and Using Triggers345 CREATE TRIGGER myTrigger6 ON
employee7 FOR UPDATE, INSERT, DELETE8 AS9 SELECT * FROM inserted10
SELECT * FROM deleted11 GO123 SELECT ‘Before INSERT’4 INSERT Employee
(ID, Name) VALUES (31, ‘Rick’)5 GO————-Before INSERT
(1 rows affected)ID name salary start_date city region———–
———- ———– ———————– ———- —— 31
Rick NULL NULL NULL NULL(1 rows affected)ID name salary start_date city region———–
(0 rows affected)12 SELECT ‘Before UPDATE’3 UPDATE Employee4 SET Name
= ‘Rickie’5 WHERE ID = 36 GO————-Before UPDATE
(1 rows affected)ID name salary start_date city region———–
———- ———– ———————– ———- —— 3
Rickie 24020 1996-12-03 00:00:00.000 Toronto W(1 rows affected)ID name salary start_date city region———–
———- ———– ———————– ———- —— 3
Celia 24020 1996-12-03 00:00:00.000 Toronto W(1 rows affected)12 SELECT ‘Before DELETE’3 DELETE FROM Employee WHERE
ID = 34 GO————-Before DELETE
(1 rows affected)ID name salary start_date city region———–
(0 rows affected)ID name salary start_date city region———–
———- ———– ———————– ———- —— 3
Rickie 24020 1996-12-03 00:00:00.000 Toronto W(1 rows affected)1
SELECT INTO 语句从三个表中接收数据，然后把多少插入另一个表中。
注明:
选用select into 做一个一时表
触发器是后生可畏种独特的储存进程，在顾客计划对点名的表施行钦点的多少修正语句时自动施行。Microsoft
SQL Server 允许为此外给定的 INSERT、UPDATE 或 DELETE 语句创制多少个触发
34 CREATE TABLE works_on (emp_no INTEGER NOT NULL,35 project_no
CHAR(4) NOT NULL,36 job CHAR (15) NULL,37 enter_date DATETIME
NULL)3839 insert into works_on values (1, ‘p1’, ‘analyst’,
‘1997.10.1’)40 insert into works_on values (1, ‘p3’, ‘manager’,
‘1999.1.1’)41 insert into works_on values (2, ‘p2’, ‘clerk’,
‘1998.2.15’)42 insert into works_on values (2, ‘p2’, NULL,
‘1998.6.1’)43 insert into works_on values (3, ‘p2’, NULL,
‘1997.12.15’)44 insert into works_on values (4, ‘p3’, ‘analyst’,
‘1998.10.15’)45 insert into works_on values (5, ‘p1’, ‘manager’,
‘1998.4.15’)46 insert into works_on values (6, ‘p1’, NULL,
‘1998.8.1’)47 insert into works_on values (7, ‘p2’, ‘clerk’,
‘1999.2.1’)48 insert into works_on values (8, ‘p3’, ‘clerk’,
‘1997.11.15’)49 insert into works_on values (7, ‘p1’, ‘clerk’,
‘1998.1.4’)50 GO(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)1 select * from works_on2 GOemp_no project_no job
enter_date———– ———- —————
———————– 1 p1 analyst 1997-10-01 00:00:00.000 1 p3
manager 1999-01-01 00:00:00.000 2 p2 clerk 1998-02-15 00:00:00.000 2
p2 NULL 1998-06-01 00:00:00.000 3 p2 NULL 1997-12-15 00:00:00.000 4 p3
analyst 1998-10-15 00:00:00.000 5 p1 manager 1998-04-15 00:00:00.000 6
p1 NULL 1998-08-01 00:00:00.000 7 p2 clerk 1999-02-01 00:00:00.000 8
p3 clerk 1997-11-15 00:00:00.000 7 p1 clerk 1998-01-04 00:00:00.000(11 rows affected)12 — Remove duplicate data and create a unique
index34 SELECT emp_no, MAX(enter_date) max_date5 INTO #works_on6
FROM works_on7 GROUP BY emp_no8 HAVING COUNT(*) 19 GO(3 rows affected)1 DELETE works_on FROM works_on, #works_on2 WHERE
works_on.emp_no = #works_on.emp_no3 AND works_on.enter_date
#works_on.max_date4 GO(3 rows affected)1 select * from works_on2 GOemp_no project_no job
enter_date———– ———- —————
———————– 1 p3 manager 1999-01-01 00:00:00.000 2 p2 NULL
1998-06-01 00:00:00.000 3 p2 NULL 1997-12-15 00:00:00.000 4 p3 analyst
1998-10-15 00:00:00.000 5 p1 manager 1998-04-15 00:00:00.000 6 p1 NULL
1998-08-01 00:00:00.000 7 p2 clerk 1999-02-01 00:00:00.000 8 p3 clerk
1997-11-15 00:00:00.000(8 rows affected)
1、INSERT触发器：能够定义二个不管哪天用INSERT语句向表中插入数据时都会执行的触发器。
当触发INSERT触发器时，新的数据行就能够被插入到触发器表和inserted表中。inserted表是一个逻辑表，它包括了早就插入的数据行的三个别本。inserted表满含了INSERT语句中已记录的插入动作。inserted表还同意援引由带头化INSERT语句而发生的日志数据。触发器通过检查inserted表来分明是不是进行触发器动作或怎么样进行它。inserted表中的行总是触发器表中意气风发行或多行的别本。
日志记录了具备改革数据的动作(INSERT、UPDATE和DELETE语句State of Qatar，但在事情日志中的消息是不行读的。然则，inserted表允许你引用由INSERT语句引起的日记变化，那样就能够将插入数据与产生的成形实行相比，来注明它们或利用更为的动作。也足以一贯援引插入的数目，而不自然它们存储到变量中。
SELECT INTO 语句常用于创设表的备份复件只怕用于对记录实行存档。