create trigger Tri_SaleItem_Update on _SaleItem INSTEAD OF Update AS BEGIN if update (ProductName) update tblSaleItem set ProductID=p.ProductID FROM tblSaleItem B,Inserted i,tblProduct p WHERE B.SaleNo=i.SaleNo and i.ProductName=m.ProductName if update(ItemCose) update tblSaleItem set ItemCose =i.ItemCose FROM tblSaleItem B,Inserted i WHERE B.SaleNo=i.SaleNoEND
create trigger Tri_SaleItem_Delete on _SaleItem INSTEAD OF Delete AS BEGIN delete tblSaleItem FROM tblSaleItem B,deleted d WHERE B.SaleNo=d.SaleNo END
有误,改为: create trigger Tri_SaleItem_Delete on _SaleItem INSTEAD OF Delete AS BEGIN delete tblSaleItem FROM tblSaleItem B,deleted d,tblProduct p WHERE B.SaleNo=d.SaleNo and b.ProductID=p.ProductID and d.ProductName=p.ProductName END gocreate trigger Tri_SaleItem_Update on _SaleItem INSTEAD OF Update AS BEGIN if update (ProductName) update tblSaleItem set ProductID=p.ProductID FROM tblSaleItem B,Inserted i,tblProduct p WHERE B.SaleNo=i.SaleNo and b.ProductID=p.ProductID and i.ProductName=p.ProductName if update(ItemCose) update tblSaleItem set ItemCose =i.ItemCose FROM tblSaleItem B,Inserted i WHERE B.SaleNo=i.SaleNoEND另外: CREATE TABLE [tblSaleItem] ( [SaleItemID] [int] IDENTITY (1, 1) NOT NULL , [SaleNo] [int] NOT NULL , [ProductID] [int] NULL , [ItemCose] [int] NULL , CONSTRAINT [PK_tblSaleItem] PRIMARY KEY CLUSTERED ( [SaleItemID] ) ON [PRIMARY] , CONSTRAINT [FK_tblSaleItem_tblSale] FOREIGN KEY ( [SaleNo] ) REFERENCES [tblSale] ( [SaleNo] ) ON DELETE CASCADE ON UPDATE CASCADE ^^^^^^^^这里的ON DELETE CASCADE ON UPDATE CASCADE 应该去掉 ) ON [PRIMARY] GO还有: 视图最好改为:CREATE VIEW dbo._SaleItem AS SELECT dbo.tblSaleItem.SaleItemID,dbo.tblSaleItem.SaleNo, dbo.tblSaleItem.ItemCose, dbo.tblProduct.ProductName FROM dbo.tblProduct INNER JOIN dbo.tblSaleItem ON dbo.tblProduct.ProductID = dbo.tblSaleItem.ProductIDGO这样,触发器要相应修改,加SaleItemID字段是为了方便的定位记录。
总结代码如下:CREATE TABLE [tblProduct] ( [ProductID] [int] IDENTITY (1, 1) NOT NULL , [ProductName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED ( [ProductID] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [tblSaleItem] ( [SaleItemID] [int] IDENTITY (1, 1) NOT NULL , [SaleNo] [int] NOT NULL , [ProductID] [int] NULL , [ItemCose] [int] NULL , CONSTRAINT [PK_tblSaleItem] PRIMARY KEY CLUSTERED ( [SaleItemID] ) ON [PRIMARY] , CONSTRAINT [FK_tblSaleItem_tblSale] FOREIGN KEY ( [SaleNo] ) REFERENCES [tblSale] ( [SaleNo] ) ) ON [PRIMARY] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOCREATE VIEW dbo._SaleItem AS SELECT dbo.tblSaleItem.SaleItemID,dbo.tblSaleItem.SaleNo, dbo.tblSaleItem.ItemCose, dbo.tblProduct.ProductName FROM dbo.tblProduct INNER JOIN dbo.tblSaleItem ON dbo.tblProduct.ProductID = dbo.tblSaleItem.ProductIDGO SET QUOTED_IDENTIFIER OFF GOcreate trigger Tri_SaleItem_INSERT on _SaleItem INSTEAD OF INSERT AS BEGININSERT INTO tblSaleItem(SaleNo,ProductID,ItemCose) SELECT SaleNo,(SELECT ProductID From tblproduct Where ProductName =inserted.productName),ItemCose FROM inserted
END gocreate trigger Tri_SaleItem_Delete on _SaleItem INSTEAD OF Delete AS BEGIN delete tblSaleItem FROM tblSaleItem B,deleted d WHERE B.SaleItemID=d.SaleItemID END gocreate trigger Tri_SaleItem_Update on _SaleItem INSTEAD OF Update AS BEGIN update tblSaleItem set ProductID=p.ProductID,ItemCose =i.ItemCose FROM tblSaleItem B,Inserted i,tblProduct p WHERE B.SaleItemID=i.SaleItemID and i.ProductName=p.ProductNameEND go
on _SaleItem
INSTEAD OF Update
AS
BEGIN
if update (ProductName)
update tblSaleItem
set ProductID=p.ProductID
FROM tblSaleItem B,Inserted i,tblProduct p
WHERE B.SaleNo=i.SaleNo
and i.ProductName=m.ProductName
if update(ItemCose)
update tblSaleItem
set ItemCose =i.ItemCose
FROM tblSaleItem B,Inserted i
WHERE B.SaleNo=i.SaleNoEND
on _SaleItem
INSTEAD OF Delete
AS
BEGIN
delete tblSaleItem
FROM tblSaleItem B,deleted d
WHERE B.SaleNo=d.SaleNo
END
create trigger Tri_SaleItem_Delete
on _SaleItem
INSTEAD OF Delete
AS
BEGIN
delete tblSaleItem
FROM tblSaleItem B,deleted d,tblProduct p
WHERE B.SaleNo=d.SaleNo
and b.ProductID=p.ProductID
and d.ProductName=p.ProductName
END
gocreate trigger Tri_SaleItem_Update
on _SaleItem
INSTEAD OF Update
AS
BEGIN
if update (ProductName)
update tblSaleItem
set ProductID=p.ProductID
FROM tblSaleItem B,Inserted i,tblProduct p
WHERE B.SaleNo=i.SaleNo
and b.ProductID=p.ProductID
and i.ProductName=p.ProductName
if update(ItemCose)
update tblSaleItem
set ItemCose =i.ItemCose
FROM tblSaleItem B,Inserted i
WHERE B.SaleNo=i.SaleNoEND另外:
CREATE TABLE [tblSaleItem] (
[SaleItemID] [int] IDENTITY (1, 1) NOT NULL ,
[SaleNo] [int] NOT NULL ,
[ProductID] [int] NULL ,
[ItemCose] [int] NULL ,
CONSTRAINT [PK_tblSaleItem] PRIMARY KEY CLUSTERED
(
[SaleItemID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblSaleItem_tblSale] FOREIGN KEY
(
[SaleNo]
) REFERENCES [tblSale] (
[SaleNo]
) ON DELETE CASCADE ON UPDATE CASCADE
^^^^^^^^这里的ON DELETE CASCADE ON UPDATE CASCADE 应该去掉
) ON [PRIMARY]
GO还有:
视图最好改为:CREATE VIEW dbo._SaleItem
AS
SELECT dbo.tblSaleItem.SaleItemID,dbo.tblSaleItem.SaleNo, dbo.tblSaleItem.ItemCose,
dbo.tblProduct.ProductName
FROM dbo.tblProduct INNER JOIN
dbo.tblSaleItem ON dbo.tblProduct.ProductID = dbo.tblSaleItem.ProductIDGO这样,触发器要相应修改,加SaleItemID字段是为了方便的定位记录。
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [tblSaleItem] (
[SaleItemID] [int] IDENTITY (1, 1) NOT NULL ,
[SaleNo] [int] NOT NULL ,
[ProductID] [int] NULL ,
[ItemCose] [int] NULL ,
CONSTRAINT [PK_tblSaleItem] PRIMARY KEY CLUSTERED
(
[SaleItemID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblSaleItem_tblSale] FOREIGN KEY
(
[SaleNo]
) REFERENCES [tblSale] (
[SaleNo]
)
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE VIEW dbo._SaleItem
AS
SELECT dbo.tblSaleItem.SaleItemID,dbo.tblSaleItem.SaleNo, dbo.tblSaleItem.ItemCose,
dbo.tblProduct.ProductName
FROM dbo.tblProduct INNER JOIN
dbo.tblSaleItem ON dbo.tblProduct.ProductID = dbo.tblSaleItem.ProductIDGO
SET QUOTED_IDENTIFIER OFF
GOcreate trigger Tri_SaleItem_INSERT
on _SaleItem
INSTEAD OF INSERT
AS
BEGININSERT INTO tblSaleItem(SaleNo,ProductID,ItemCose)
SELECT SaleNo,(SELECT ProductID From tblproduct Where ProductName =inserted.productName),ItemCose FROM inserted
END
gocreate trigger Tri_SaleItem_Delete
on _SaleItem
INSTEAD OF Delete
AS
BEGIN
delete tblSaleItem
FROM tblSaleItem B,deleted d
WHERE B.SaleItemID=d.SaleItemID
END
gocreate trigger Tri_SaleItem_Update
on _SaleItem
INSTEAD OF Update
AS
BEGIN
update tblSaleItem
set ProductID=p.ProductID,ItemCose =i.ItemCose
FROM tblSaleItem B,Inserted i,tblProduct p
WHERE B.SaleItemID=i.SaleItemID
and i.ProductName=p.ProductNameEND
go