SQL完整性

create table test
(
  sno varchar(10) primary key,
  sn nvarchar(10) ,
  sex char(2) ,
  age int,
  dept nvarchar(10),
  check(sex='男' or sn not like '张%'),
)

insert into test(sno,sn,sex) values('002','张斯','女')

create table test1
(
  id int identity(1,1) primary key,
  salary int,
  deduct int, 
  check(salary-deduct>=1000)
)

insert into test1(salary,deduct) values(1500,750)

go
create trigger trInsert on test1
for insert as
begin
  update test1 set salary=0 where salary<0
end

insert into test1(salary,deduct) values(-100,-2000)
select * from test1

go
create trigger trInsert1 on test1
for insert as
begin
  if (day(getdate())!=5)
    begin
      print 'error'
   rollback transaction
    end
end

insert into test1(salary,deduct) values(-100,-2000)

create table test2
(
  id int identity(1,1) primary key,
  salary int,
  deduct int 
  --check(salary-deduct>=1000)
)

go
create trigger trInsert2 on test2
for insert,update 
as
begin
  declare @salary int,@deduct int
  select @salary=salary,@deduct=deduct from inserted
  if (@salary-@deduct<1000)
    begin
      print 'error'
   rollback transaction
    end
end

insert into test2(salary,deduct) values(2000,1200)

create table test3
(
  id int identity(1,1) primary key,
  myId int not null unique,
)
insert into test3(myid) values(1)
insert into test3(myid) values(2)

create table test4
(
  id int identity(1,1) primary key,
  myForeignId int references test3(myId) on delete cascade on update cascade
)
insert into test4(myForeignId) values(1)
insert into test4(myForeignId) values(2)
select * from test4
update test3 set myId=3 where myId=1
select * from test4
delete from test3 where myId=2

create table test5
(
  id int identity(1,1) primary key,
  myId int not null unique,
)
insert into test5(myid) values(1)
insert into test5(myid) values(2)

create table test6
(
  id int identity(1,1) primary key,
  myForeignId int
)

insert into test6(myForeignId) values(1)
insert into test6(myForeignId) values(2)

go
create trigger trDelete on test5
for delete 
as
begin
  declare @myId int
  select @myId=myId from deleted
  delete from test6 where myForeignId=@myId
end

select * from test6
delete from test5 where myId=1
select * from test6