October 13, 2016 at 11:00 pm
I have a table named purchasedetail with fields ProductCode, Quantity
and a table named Products fields ProductCode, StockInHand
Now I want to write a trigger on delete on purchasedetail that StockInHand field updated when multiple row deletion in purchasedetail table
Plz guid me. I am newbie to SQL Server
October 14, 2016 at 2:05 am
Keep it simple, an update to Products from deleted, use the pseudotable to see the quantity on the deleted rows.
No cursors, no loops!
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2016 at 2:06 am
Here is an example that you can use:
-- This is going to be your database
-- I'm using tempdb for this example
USE tempdb;
GO
-- I will create the tables for the sake of this example
-- you already have the tables so these teps will be skipped
IF OBJECT_ID('purchasedetail') IS NULL
BEGIN
CREATE TABLE purchasedetail (
ProductCode int,
Quantity int
);
END
GO
IF OBJECT_ID('products') IS NULL
BEGIN
CREATE TABLE products (
ProductCode int,
StockInHand int
);
END
GO
-- Here we go with the trigger.
CREATE TRIGGER updateStock ON purchasedetail
AFTER DELETE
AS
BEGIN
-- This instruction is important and should be included in all
-- your triggers to avoid interfering with some data providers
-- that don't like row counts being returned
SET NOCOUNT ON;
-- Now you need to update the quantity in stock
-- Don't assume that a single row will be deleted: when
-- multiple rows are deleted, you have to process them all
-- using the DELETED logical table. This table contains
-- one row for each row which has been deleted from the
-- source table.
UPDATE pro
SET StockInHand -= del.Total_Quantity
FROM products AS pro
INNER JOIN (
-- Let's sum the quantities deleted from the purchasedetail table
-- grouping by productcode
SELECT ProductCode,
SUM(Quantity) AS Total_Quantity
FROM deleted
GROUP BY ProductCode
) AS del
ON pro.ProductCode = del.ProductCode;
END
GO
If you don't understand the code DON'T USE IT: come back here and ask questions. You don't want to support code that you don't understand, do you?
Hope this helps
Gianluca
-- Gianluca Sartori
October 14, 2016 at 2:14 am
One more thing: what happens when a row is updated in purchasedetail? Does that quantity have to be updated in products too?
And what happens when you insert a new row?
Generally speaking, triggers are a very bad place to implement business logic, because they make the code difficult to debug/follow. They are some sort of "hidden" code that is executed even in situations when you would like them to be skipped.
Implementing your business logic in "explicit" code (say stored procedures or your favourite programming language) is a better practice.
Triggers are usually a "last resort" when changing everything else is impossible (third party code) or too expensive.
-- Gianluca Sartori
October 14, 2016 at 4:43 am
Can you give me example code Plz
October 14, 2016 at 5:05 am
tahirfarooq (10/14/2016)
Can you give me example code Plz
Gianluca gave you an example you can start from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2016 at 5:13 am
Thank you for your assistance. It helps me a lot
October 14, 2016 at 10:54 am
spaghettidba (10/14/2016)
Generally speaking, triggers are a very bad place to implement business logic, because they make the code difficult to debug/follow. They are some sort of "hidden" code that is executed even in situations when you would like them to be skipped.
Implementing your business logic in "explicit" code (say stored procedures or your favourite programming language) is a better practice.
Triggers are usually a "last resort" when changing everything else is impossible (third party code) or too expensive.
As a DBA (rather than a developer), I deeply disagree. Logic like that should be put where it always runs and it always runs the same way. That's perfect for a trigger. There may be thirty ways to add or remove data, but I want a single, consistent method for calculating totals based on that data.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply