CREATE TABLE Books( /*创建图书表*/
BookID CHAR(15) PRIMARY KEY CLUSTERED,
BookName CHAR(50) NOT NULL,
Class CHAR(20) NOT NULL,
ISBN CHAR(30) NOT NULL UNIQUE,
InLib BIT DEFAULT 1,
KeyWord CHAR(50) )
CREATE TABLE BookState( /*创建书目状态表表*/
BookID CHAR(15) NOT NULL FOREIGN KEY REFERENCES Books(BookID)
ON DELETE CASCADE ON UPDATE CASCADE,
StudentID SMALLINT,
RegTime SMALLDATETIME NOT NULL,
LendTime SMALLDATETIME,
OverTime BIT DEFAULT 0,
Frequence SMALLINT DEFAULT 0 NOT NULL
)还是贴出来吧,请问哪里写得不对?
BookID CHAR(15) PRIMARY KEY CLUSTERED,
BookName CHAR(50) NOT NULL,
Class CHAR(20) NOT NULL,
ISBN CHAR(30) NOT NULL UNIQUE,
InLib BIT DEFAULT 1,
KeyWord CHAR(50) )
CREATE TABLE BookState( /*创建书目状态表表*/
BookID CHAR(15) NOT NULL FOREIGN KEY REFERENCES Books(BookID)
ON DELETE CASCADE ON UPDATE CASCADE,
StudentID SMALLINT,
RegTime SMALLDATETIME NOT NULL,
LendTime SMALLDATETIME,
OverTime BIT DEFAULT 0,
Frequence SMALLINT DEFAULT 0 NOT NULL
)还是贴出来吧,请问哪里写得不对?
ALTER TABLE [dbo].[Bookstate] ADD
CONSTRAINT [FK_Bookstate_Books] FOREIGN KEY
(
[BooksID]
) REFERENCES [dbo].[Books] (
[BooksID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
可是插入怎么办?我是想两表同时插入记录。
写了一个视图,但是插入报错
CREATE VIEW Up AS
SELECT Books.BookID,BookName,Class,ISBN,KeyWord,RegTime
FROM Books,BookState
WHERE Books.BookID=BookState.BookID用视图可以做到同时插入吗?
另外,想了一下两个表应该是1对1的关系
FOR INSERT
AS
insert db2..tabSlave select * from insertedCREATE TRIGGER 名2 ON dbo.tabMain
FOR update
AS
update db2..tabSlave set 列=aa.列 from inserted aa where aa.编号=db2..tabSlave.编号
CREATE TRIGGER 名3 ON dbo.tabMain
FOR delete
AS
delete db2..tabSlave where 编号 in (select 编号 from deleted)
CREATE TRIGGER InsertBooks On Up
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Books(BookID,BookName,Class,ISBN,KeyWord) SELECT BookID,BookName,Class,ISBN,KeyWord FROM INSERTED
INSERT INTO BookState(BookID,RegTime) SELECT BookID,RegTime FROM INSERTED
END 睡醒了结贴,各位看着哪里有毛病请指点一下,再次感谢!!!!
对INSERT和UPDATE强制关系是什么意思?
级联更新只是对UPDATE有作用,INSERT不起作用是吗?