表A
F_code F_name F_bookdate
001 aaa 2007-05-01 11:22:22
002 bbb 2007-05-02 12:11:33
003 ccc 2007-05-03 13:11:22
004 ddd 2007-05-04 16:55:12表B
F_code F_goods
001 1111
002 2222
003 3333
003 4444
004 5555 表A与表B通过F_code 关联,我想删除2007-05-01至2007-05-02 的表A与表B中的记录怎么写?
F_code F_name F_bookdate
001 aaa 2007-05-01 11:22:22
002 bbb 2007-05-02 12:11:33
003 ccc 2007-05-03 13:11:22
004 ddd 2007-05-04 16:55:12表B
F_code F_goods
001 1111
002 2222
003 3333
003 4444
004 5555 表A与表B通过F_code 关联,我想删除2007-05-01至2007-05-02 的表A与表B中的记录怎么写?
关联的实现:
1.创建表:
create table t1(c1 int primary key,c2 int);
create table t2(d1 int references t1(c1) on delete cascade,d2 int);//插入数据:
insert into t1 values(1,1);
insert into t1 values(2,2);insert into t2 values(2,3);//删除数据
delete from t1 where c2=2select * from t2
//发现数据(2,3)被级连删除。
insert @ta
select '001', 'aaa', '2007-05-01 11:22:22' union all
select '002', 'bbb', '2007-05-02 12:11:33' union all
select '003', 'ccc', '2007-05-03 13:11:22' union all
select '004', 'ddd', '2007-05-04 16:55:12'
declare @tb table(F_code varchar(10),F_goods int)
insert @tb
select '001', 1111 union all
select '002', 2222 union all
select '003', 3333 union all
select '003', 4444 union all
select '004', 5555 ----删除B表
delete b from @tb as b inner join @ta as a on b.F_code = a.F_code
and convert(varchar(10),a.F_bookdate,120) between '2007-05-01' and '2007-05-02'
----删除A表
delete from @ta where convert(varchar(10),F_bookdate,120) between '2007-05-01' and '2007-05-02'
----查看
select * from @ta
select * from @tb
F_code F_name F_bookdate
---------- ---------- ------------------------
003 ccc 2007-05-03 13:11:22.000
004 ddd 2007-05-04 16:55:12.000
F_code F_goods
---------- -----------
003 3333
003 4444
004 5555
*/
當 delete A where F_bookdate >= '2007-05-01' and F_bookdate<= '2007-05-02'時觸發
create trigger trg_B on table A for delete
as
delete B where B.F_code = (select F_code from deleted)
存储过程吧!
create proc ttt(date1 varchar(10),date2 varchar(10))
as
beginexec('delete b from @tb as b inner join @ta as a on b.F_code = a.F_code
and convert(varchar(10),a.F_bookdate,120) between '''+@date1+''' and '''+@date2+''';
delete from @ta where convert(varchar(10),F_bookdate,120) between '''+@date1+''' and '''+@date2+''')
end
delete a from a,b
where a.f_code=b.f_code
and f_bookdate between '2007-05-01' and '2007-05-02'
delete b from a,b
where a.f_code=b.f_code
and f_bookdate between '2007-05-01' and '2007-05-02'
create table a
(
F_code varchar(10),
F_name varchar(10),
F_bookdate datetime
)
go
insert into a
select '001','aaa','2007-05-01 11:22:22' union all
select '002','bbb','2007-05-02 12:11:33' union all
select '003','ccc','2007-05-03 13:11:22' union all
select '004','ddd','2007-05-04 16:55:12'
go
create table b
(
F_code varchar(10),
F_goods varchar(10)
)
insert into b
select '001','1111' union all
select '002','2222' union all
select '003','3333' union all
select '004','4444' union all
select '004','5555'
godelete from b from a left outer join b on a.F_code = b.F_code where a.F_bookdate between '2007-05-01 00:00:01' and '2007-05-02 23:59:59'
delete from a from a left outer join b on a.F_code = b.F_code where a.F_bookdate between '2007-05-01 00:00:01' and '2007-05-02 23:59:59'select * from a inner join b on a.F_code=b.F_code
/*
---------- ---------- ----------------------- ---------- ----------
003 ccc 2007-05-03 13:11:22.000 003 3333
004 ddd 2007-05-04 16:55:12.000 004 4444
004 ddd 2007-05-04 16:55:12.000 004 5555
*/