Cao Thủ Vào Nhờ Chút

[ccchua]

----The shippment date should be updated to two days after the transaction date after the transaction is successfully completed ---
Có 2 Bảng là: InventoryTransactions và PurchaseOrders. Có trigger như sau:

Create table InventoryTransactions
(
cTransactionID char(6) not null constraint pk_Tran primary key,
cItemID char(6) not null,
cPurchaseOrderID char(6) not null,
dTransactionDate datetime default(getdate()),
vTransactionDescription varchar(50),
nQuantityOrdered numeric,
nQuantityReceived numeric,
mTotalAmount money,
Constraint fk_Item foreign key(cItemID) references Items(cItemID)
)


Create table PurchaseOrders
(
cPurchaseOrderID char(6) not null constraint pk_Pur primary key,
cSupplierID char(6),
cEmployeeID char(6),
cShippingMethodID char(6),
dShippingDate datetime,
dOrderDate datetime default(getdate()),
mFreightCharge money,
constraint fk_Supply foreign key(cSupplierID) references Suppliers(cSupplierID)
)

CREATE TRIGGER trgUpdateShipment
ON InventoryTransactions
FOR INSERT, UPDATE
AS
BEGIN

DECLARE @PurchaseOrderId char(6)
SELECT @PurchaseOrderId = Inserted.cPurchaseOrderID FROM Inserted join PurchaseOrders
ON Inserted.cPurchaseOrderId = PurchaseOrders.cPurchaseOrderId

DECLARE @TransactionDate datetime
SELECT @TransactionDate = dTransactionDate FROM Inserted
Where cTransactionID = (select cTransactionID from Inserted)

UPDATE PurchaseOrders
SET dShippingDate = @TransactionDate + 2
WHERE cPurchaseOrderId = @PurchaseOrderId
END


Làm thế này đúng không nhi? Sao nó không cộng thêm 2 ngày được, hic

[xbacala]

try this:

CREATE TRIGGER trgUpdateShipment
ON InventoryTransactions
FOR INSERT, UPDATE
AS
BEGIN

UPDATE po
SET dShippingDate =DATEADD(day,2,i.dTransactionDate)
FROM PurchaseOrders po INNER JOIN inserted i ON i.cPurchaseOrderID =po.cPurchaseOrderID

END

Chú ý là trigger nó sẽ không bị fired nếu you dùng Enterprise Mânger để thao tác data.