Home
/
SQL

SQL Recursive trigger

Recursive trigger

USE pubs 
GO

CREATE TABLE BUDGET
    (dept_name      varchar(30)    not null,
     parent_name    varchar(30)    null,
     budget_amt     money          not null)
GO
INSERT INTO budget values ('Internal Training', 'Training', $10) 
INSERT INTO budget values ('Training', 'Services', $100) 
INSERT INTO budget values ('Services', NULL, $500) 
GO
CREATE TRIGGER update_budget
ON budget FOR update AS
DECLARE @rows int
SELECT @rows = @@ROWCOUNT
IF ( @rows=0) RETURN
IF ( @rows > 1) BEGIN
    PRINT 'Only one row can be updated at a time'
    ROLLBACK TRAN
    RETURN
END
IF (SELECT parent_name FROM inserted) IS NULL RETURN 
UPDATE budget 
    SET budget_amt = budget_amt + (SELECT budget_amt FROM inserted) - 
                                  (SELECT budget_amt FROM deleted)
WHERE dept_name = (SELECT parent_name FROM inserted)
GO

EXEC sp_dboption pubs, 'recursive triggers', true
GO

-- Now test the trigger
SELECT * FROM budget
GO

UPDATE budget
SET budget_amt = budget_amt + 10
WHERE dept_name = 'Internal Training'
GO

-- Now check to see which rows have changed
SELECT * FROM budget
GO

-- Now decrement a value at an intermediate level
UPDATE budget
SET budget_amt = budget_amt - 50
WHERE dept_name = 'Training'
GO

-- Now check to see which rows have changed
SELECT * FROM budget
GO

Related Snippets