如何将主键表中的记录删除之后,外建表中的相应记录都删除?例如在员工的基本资料表中有编号、姓名等等,编号是主键
在员工培训表中有员工编号、培训内容等,其中员工编号是基本资料表中编号的外键
现在我想做的是删除编号为?的所有信息
我要怎么做?请各位高手帮小弟一下,不胜感激!
在员工培训表中有员工编号、培训内容等,其中员工编号是基本资料表中编号的外键
现在我想做的是删除编号为?的所有信息
我要怎么做?请各位高手帮小弟一下,不胜感激!
解决方案 »
- 触发器
- 个人认为极复杂的数据库交叉表,烦请各位帮忙解决下!急!
- 大侠救命,我在还原数据库时,出现错误"The LSN(206:74:14) passed to log scan in database 'databasename' is invalid",不能还原数据库了!
- 求一sql語句......
- sql 2000如何强行断开用户对某个数据库的边接?
- 在SQL查询中,如何在结果集中,计算一条记录具体重复多少次?
- Sql
- 分布式事务提交问题
- office2003导致的问题,急
- 怎么改写这个SQL语句让它运行不超时?
- 高手们,帮我看看下面存储过程为什么会有死循环?
- 在VB关于多表联系删除一条记录的问题
insert a
select 1create table b(id int foreign key references a(id) on delete cascade)
insert b
select 1--select * from a
--select * from b
delete a
select * from a
select * from bdrop table b,a
(
productid int,
productname nvarchar(20),
unitprice money,
quanlity int,
PRIMARY KEY (productid)
)CREATE TABLE employees
(
employeeid int,
employeename nvarchar(20),
telphone nvarchar(15) CONSTRAINT [DF_employees_telphone] DEFAULT ('27-85773750'),
age int CHECK (age<35),
address nvarchar(120) UNIQUE,
PRIMARY KEY (employeeid)
)CREATE TABLE orders
(
orderid int,
employeeid int,
productid int,
date datetime,
quanlity int,
PRIMARY KEY (orderid),
FOREIGN KEY (employeeid) REFERENCES employees(employeeid)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (productid) REFERENCES products(productid)
ON UPDATE CASCADE
ON DELETE CASCADE
)--3
INSERT products
SELECT 1, 'P1', 1.00, 100 UNION ALL
SELECT 2, 'P2', 2.00, 200 UNION ALL
SELECT 3, 'P3', 3.00, 300INSERT employees
SELECT 1, 'E1', '027-85773723', '20', 'ABC' UNION ALL
SELECT 2, 'E2', '027-85773723', '30', 'EFG' UNION ALL
SELECT 3, 'E3', '027-85773723', '22', 'UIJ'INSERT orders
SELECT 1, 1, 1, '2008-05-11',11 UNION ALL
SELECT 2, 2, 2, '2008-05-12',22 UNION ALL
SELECT 3, 3, 3, '2008-05-13',33delete from products where productid=1select * from orders--同时删除了orders中productid=1的记录orderid employeeid productid date quanlity
----------- ----------- ----------- ----------------------- -----------
2 2 2 2008-05-12 00:00:00.000 22
3 3 3 2008-05-13 00:00:00.000 33(2 行受影响)
create trigger Trg on products for delete
as
begin
delete from orders where productid in (select productid from deleted)
end
godelete from products where productid=2 --利用触发器关联删除,从上面剩下的两条中删除productid=2的
select * from ordersorderid employeeid productid date quanlity
----------- ----------- ----------- ----------------------- -----------
3 3 3 2008-05-13 00:00:00.000 33
(1 行受影响)