OUTPUT做多少操作记录,创造与用法

 

sql 触发器trigger 创建与用法

OUTPUT 子句

发器也是一种带名的pl/sql块。触发器类似于过程和函数,因为它们都是拥有声明

可以在数据进行增删改的时候,可以返回受影响的行。先准备一张表

、执行和异常处理过程的带名pl/sql块。与包类似,触发器必须存储在数据库教程中并

create table #t
(
    id int identity primary key 
    ,name varchar(100)
)
go

且不能被块进行本地化声明。


对于触发器而言,当触发事件发生的时候就会显式地执行该触发器,并且触发器不

 

接受参数

1、insert ,影响行在inserted表里

create table employee(2 id int,3 name nvarchar (10),4 salary int,5
start_date datetime,6 city nvarchar (10),7 region char (1))8 go12
insert into employee (id, name, salary, start_date, city,

region)3 values (1, ‘jason’, 40420, ’02/01/94′, ‘new york’,

‘w’)4 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (2, ‘robert’,14420, ’01/02/95′,

‘vancouver’,’n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (3, ‘celia’, 24020, ’12/03/96′, ‘toronto’,

‘w’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (4, ‘linda’, 40620, ’11/04/97′, ‘new york’,

‘n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (5, ‘david’, 80026, ’10/05/98′,

‘vancouver’,’w’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (6, ‘james’, 70060, ’09/06/99′, ‘toronto’,

‘n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (7, ‘alison’,90620, ’08/07/00′, ‘new york’,

‘w’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (8, ‘chris’, 26020, ’07/08/01′,

‘vancouver’,’n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city,

region)2 values (9, ‘mary’, 60020, ’06/09/02′, ‘toronto’,

‘w’)3 go

(1 rows affected)12 select * from employee3 goid name salary
start_date city

region———– ———- ———– ———————–


—– 1 jason 40420 1994-02-01 00:00:00.000 new york w 2 robert 14420
1995-01-02 00:00:00.000 vancouver n 3 celia 24020 1996-12-03
00:00:00.000 toronto w 4 linda 40620 1997-11-04 00:00:00.000 new york
n 5 david 80026 1998-10-05 00:00:00.000 vancouver w 6 james 70060
1999-09-06 00:00:00.000 toronto n 7 alison 90620 2000-08-07
00:00:00.000 new york w 8 chris 26020 2001-07-08 00:00:00.000
vancouver n 9 mary 60020 2002-06-09 00:00:00.000 toronto w

(9 rows affected)12 — 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)123 drop table employee4 go1

insert into #t(name)
output inserted.*
values('a')

dml触发器的激活顺序

返回结果:

1)执行before语句级触发器如果存在这种触发器

id name


1 a

批量插入:

insert into #t
output inserted.*
select 'b'

2)对受该语句影响的每一行记录

id name


2 b

 


 

2、delete ,影响行在deleted表里

delete from #t
output deleted.id
where id = 1

返回结果:

执行before行级触发器如果存在这种触发器

id

1

 


 

3、update,会将新数据放在inserted表里,老数据放在deleted表里

update #t
set name='new value'
OUTPUT deleted.id,deleted.name,inserted.id,inserted.name  
where id=2

执行该语句本身

www.350.vip ,id name id name




2 d 2 new value

(1 row(s) affected)

 


 

4、OUTPUT INTO 支持将数据 插入到表里

DECLARE @outputTable TABLE(name1 varchar(100),name2 varchar(100))

update #t
set name='new value 3'
OUTPUT deleted.name,inserted.name into @outputTable
where id=2

SELECT * FROM @outputTable

(1 row(s) affected)

执行after行级触发器–如果存在这种触发器

name1 name2


new value new value 3

(1 row(s) affected)

 

 

)

 

3)执行after语句级触发器–如果存在这种触发器

同一种类型的触发器的点火次序没有经过定义。如果该次序很重要的话,那么建议

将所有这些操作组合到一个触发器当中。

3、行级触发器中的关联标识符

触发器的激活语句每处理一行数据,行级触发器就会激活一次。可以在这种行级触

发器内部,访问正被处理的记录行中的数据。这是通过两个关联标识符–:old

发表评论

电子邮件地址不会被公开。 必填项已用*标注