Trigger
Hi Zoran, I hope you are doing well. Would you mind having a look at the following issue? There are two tables: Order Table and Customer Table. The primary key in Order table is Order ID, which is linked as a foreign key in Customer Table. Customer Table includes a column that stores the data for the last order that the customer placed (LastOrderPlaced). Now, if I want to update the value for the LastOrderPlaced in Customer Table when a new order is entered into Order Table, which trigger do you think I should use? I think I should use INSTEAD OF UPDATE trigger because while the trigger is fired by Order Table, the trigger itself should be at Customer Table. In Customer Table, we update value rather than insert. However, some people said that I should use AFTER INSERT trigger. How do you think? Many thanks, John
Zoran Barac 12:56 AM Hi John, if I understand you well Order TAB is parent table holding primary key OrderID and foreign key constraint on Customer TAB ... then it should be AFTER INSERT TRIGGER ... something like ...
CREATE TABLE [dbo].[Order]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [Order] [nvarchar](50) NULL, CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
([OrderID] ASC) ) ON [PRIMARY] GO CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [LastOrderPlaced] [datetime] NOT NULL, [OrderID] [int] NOT NULL, CONSTRAINT [PK_Customer]
PRIMARY KEY CLUSTERED ([CustomerID] ASC) ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Customer]
WITH CHECK
ADD CONSTRAINT [FK_Customer_OrderID] FOREIGN
KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID]) GO
ALTER TABLE [dbo].[Customer]
CHECK CONSTRAINT [FK_Customer_OrderID] GO CREATE TRIGGER AfterInsertTrigger ON [Order] AFTER INSERT AS DECLARE @ID int; SELECT @ID=i.OrderID from inserted i INSERT INTO [dbo].[Customer] ([LastOrderPlaced],
[OrderID]) VALUES (GETDATE(),@ID) GO INSERT INTO [dbo].[Order] ([Order])
VALUES ('Order01') GO SELECT [OrderID],[Order]
FROM [DBATools].[dbo].[Order] SELECT [CustomerID] ,[LastOrderPlaced], [OrderID]
FROM [DBATools].[dbo].[Customer] GO you are inserting data just in order table ... trigger is doing rest of the work
Zoran Barac 7:57 AM
Yeah, initial insert is on parent table. In your task/question they usualy specify table name where you need to create triger. Cheers
The same thing here, if we want to update quantity column in an Inventory Table (dbo.Inventory) when a product is added to Order Table (dbo.Order), we should create a Trigger on Order Table, which actually fire the Trigger. As follows,
Create Trigger_Order_Inventory_Check On dbo.Order AFTER INSERT
AS BEGIN SET NOCOUNT ON
DECLARE
@OrderQty int
@InvQty int
@ProdID int
SELECT @OrderQty = i.Quantity @InvQty = inv.Quantity @ProdID = i.ProdID FROM inserted AS i JOIN dbo.inventory AS inv ON i.ProdID = inv.ProdID If @OrderQty > @InvQty BEGIN Print 'Low Inventory' Rollback transaction END ELSE BEGIN UPDATE dbo.Inventory SET Quantity = @InvQty - @OrderQty WHERE ProdID = @ProdID PRINT 'Order Complete' END END