示例.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'