示例.txt

主要介绍了sql的基础教程

--触发器只能在当前数据库中创建。 但是，触发器可以引用其他数据库中的对象。(示例)
use sampledb
go

创建表test备用
create table test
(
 aa int,
 bb int
)
go

向test表中插入一些数据备用
insert into test values (1001,0)
insert into test values (1002,0)
insert into test values (1003,0)

创建另一个库备用
create database testdb
go

use testdb
go

在库testdb中再创建一个表备用
create table test_11
(
 aa int,
 bb int
)
go

在testdb库中的表test_11上创建一个insert触发器
use testdb
go

if exists(select name from sysobjects where name ='tri_test' and type='tr')
drop trigger tri_test
go

create trigger tri_test on test_11
for insert 
as
  update sampledb.dbo.test
  set bb=bb+(select bb from inserted)
  where aa= (select aa from inserted)

测试触发器的工作情况
insert into test_11 values (1002,2)

insert into test_11 values (1001,1)






-----------------------------





触发器嵌套示例

use sampledb
go
建立触发器
create table testa
(
   a_id char(1),
   a_name char(2)
)
insert into testa values('1','1')
insert into testa values('2','2')
insert into testa values('3','3')

create table testb
(
   b_id char(1),
   b_name char(2)
)
insert into testb values('1','1')
insert into testb values('2','2')
insert into testb values('3','3')

create table testc
(
   c_id char(1),
   c_name char(2)
)
insert into testc values('1','1')
insert into testc values('2','2')
insert into testc values('3','3')

触发器嵌套示例(1)
create trigger del_testa
on testa
instead of delete
as
  delete testb 
  where b_id in 
	(select a_id from deleted)

create trigger del_testb
on testb
instead of delete
as
  delete testc 
  where c_id in 
	(select b_id from deleted)

触发器嵌套示例测试(1)
delete testa where a_id = '1'

-- drop trigger del_testa2
-- drop trigger del_testb2
----------------------------------------------------------------------------------------------------------------------

触发器嵌套示例(2)
create trigger del_testa2
on testa
for delete
as
  delete testb 
  where b_id in 
	(select a_id from deleted)

create trigger del_testb2
on testb
for delete 
as
 delete testc
  where c_id in 
	(select b_id from deleted)

触发器嵌套示例测试(1)
delete testa where a_id = '1'