SQL Server Trigger After Insert Update with Examples

In this SQL Server video tutorial, I will explain how to create an AFTER Trigger in SQL Server for INSERT and UPDATE operations. And I will explain how to create an AFTER Trigger in SQL Server for all INSERT, UPDATE, and DELETE operations.

Here is the list of queries that I will cover
1. SQL Server Trigger After Insert Update
2. SQL Server Trigger After Insert Update Example
3. SQL Server Trigger After Insert Update Delete
4. SQL Server Trigger After Update Inserted Table
5. SQL Server Trigger After Insert Update Another Table
6. SQL Server Trigger After Insert Update Specific Column
7. SQL Server Trigger After Insert Update Same Table
8. SQL Server Trigger After Insert Update Timestamp
++++++++++++++++++++++++++++++++++++
Check out the complete tutorial on SQL Server Trigger After Insert Update with Examples
++++++++++++++++++++++++++++++++++++
Check out previous SQL Server tutorials
++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++
Subscribe to Our YouTube Channel for more videos on Python, Blockchain, Bitcoin, Solidity, Ethereum, Cryptocurrency, Azure SQL, SQL Server, PostgreSQL, MongoDB, Oracle, MariaDB, etc

\\ Playlist
+++++++++++++++++++++++
#sqlserver
#sqlstoredprocedure
#storedprocedure
#SQLServertriggerafterinsertupate

Do Visit Our Websites

[hasanmougharbel]

Hey my friend, blessed to discover your channel.
I have only a simple enquiry as a newbie sql learner.
What is the main difference between alter and update in terms of their application in sql?
Thanks for taking time on this..

[kumarVinith]

Hi,

I have written a Trigger for both insert and Update operation.
But my trigger only work for Insert operation. Not working for Update operation.

This is my trigger.

Please review this trigger query at your convenience and let me know where I might be going wrong.


ALTER TRIGGER
ON [dbo].[StockTransaction]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

PRINT 'Trigger UpdateStockTransactionDetails fired';

DECLARE @ItemId INT;
DECLARE @StockTransactionId INT;

DECLARE InsertedCursor CURSOR FOR
SELECT i.ItemId, i.Id -- Assuming Id is StockTransactionId
FROM inserted i;

OPEN InsertedCursor;
FETCH NEXT FROM InsertedCursor INTO @ItemId, @StockTransactionId;

BEGIN

;WITH NumberedRecords AS (
SELECT
std.Id,
std.ItemId,
std.StockTransactionId,
std.TransactionDate,
std.Quantity,
std.OpeningStock,
std.ClosingStock,
std.TypeId,
ROW_NUMBER() OVER (PARTITION BY std.ItemId ORDER BY std.TransactionDate, std.Id) AS RowNum
FROM
std
INNER JOIN
[dbo].[StockTransaction] st ON std.StockTransactionId = st.Id
WHERE
std.ItemId = @ItemId AND std.StockTransactionId = @StockTransactionId
),
RecursiveStock AS (
-- Anchor member: first record for each ItemId
SELECT
nr.Id,
nr.ItemId,
nr.StockTransactionId,
nr.TransactionDate,
nr.Quantity,
CAST(CASE
WHEN nr.RowNum = 1 THEN st.OpeningStockQty
ELSE nr.OpeningStock
END AS DECIMAL(18, 2)) AS OpeningStock,
CAST(CASE
WHEN nr.RowNum = 1 THEN
CASE WHEN nr.TypeId IN (1, 3, 7) THEN st.OpeningStockQty + nr.Quantity
ELSE st.OpeningStockQty - nr.Quantity
END
ELSE
CASE WHEN nr.TypeId IN (1, 3, 7) THEN nr.OpeningStock + nr.Quantity
ELSE nr.OpeningStock - nr.Quantity
END
END AS DECIMAL(18, 2)) AS ClosingStock,
nr.RowNum
FROM
NumberedRecords nr
INNER JOIN
[dbo].[StockTransaction] st ON nr.StockTransactionId = st.Id
WHERE
nr.RowNum = 1

UNION ALL

-- Recursive member: subsequent records
SELECT
nr.Id,
nr.ItemId,
nr.StockTransactionId,
nr.TransactionDate,
nr.Quantity,
CAST(rs.ClosingStock AS DECIMAL(18, 2)) AS OpeningStock,
CAST(CASE
WHEN nr.TypeId IN (1, 3, 7) THEN rs.ClosingStock + nr.Quantity
ELSE rs.ClosingStock - nr.Quantity
END AS DECIMAL(18, 2)) AS ClosingStock,
nr.RowNum
FROM
NumberedRecords nr
INNER JOIN
RecursiveStock rs ON nr.ItemId = rs.ItemId
AND nr.StockTransactionId = rs.StockTransactionId
AND nr.RowNum = rs.RowNum + 1
)
-- Update the StockTransactionDetails table with the computed values
UPDATE std
SET
std.OpeningStock = rs.OpeningStock,
std.ClosingStock = rs.ClosingStock
FROM
std
INNER JOIN
RecursiveStock rs ON std.Id = rs.Id;

PRINT 'Updated StockTransactionDetails';

-- Update the ClosingStockQty in StockTransaction with the last record's ClosingStock in StockTransactionDetails
;WITH LastRecord AS (
SELECT
std.Id,
std.StockTransactionId,
std.ClosingStock,
ROW_NUMBER() OVER (PARTITION BY std.StockTransactionId ORDER BY std.TransactionDate DESC, std.Id DESC) AS RowNum
FROM
std
WHERE
std.StockTransactionId = @StockTransactionId
)
UPDATE st
SET
st.ClosingStockQty = lr.ClosingStock
FROM
[dbo].[StockTransaction] st
INNER JOIN
LastRecord lr ON st.Id = lr.StockTransactionId
WHERE
lr.RowNum = 1;

PRINT 'Updated StockTransaction ClosingStockQty';

FETCH NEXT FROM InsertedCursor INTO @ItemId, @StockTransactionId;
END;

CLOSE InsertedCursor;
DEALLOCATE InsertedCursor;
END;