Published on: 17 September 2007 By: Ahsan Khan
A trigger is fired when an INSERT, UPDATE or DELETE occurs in a table.
Download the script for this trigger:
sql-triggers.zip
In the example below tr_order_number_ins increments the to_order_number column by 1 each time an INSERT occurs in the table 'orders'.
EXAMPLE:
CREATE TABLE "orders"(
to_guid uniqueidentifier,
to_order_number varchar(1024),
to_ts timestamp
)
GO
CREATE TRIGGER [dbo].[tr_order_number_ins]
ON [dbo].[orders]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @max_line_number int
SELECT @max_line_number = MAX(to_order_number) FROM orders
IF @max_line_number IS NULL
BEGIN
SET @max_line_number = 1000
END
ELSE
BEGIN
SET @max_line_number = @max_line_number + 1
END
UPDATE orders SET
to_order_number = @max_line_number
WHERE to_guid = (SELECT to_guid FROM INSERTED)
SET NOCOUNT OFF
END
View All Articles (Articles Archive)