E
E
Egorithm2016-10-08 13:13:18
SQL Server
Egorithm, 2016-10-08 13:13:18

How to learn to the trigger value of a field of a deleted line?

Such a task:

Create a trigger that will automatically increment or decrement [Number of Employees] in the [Departments] table when adding or deleting records in the [Employees] table.

For clarity:
576e0f43d6ec451d862870276312d453.png
That is, if an employee is removed from a department, then this department has a code (a foreign key in the Employees table). And, according to this code, in the Departments table, you need to decrement (or increment, if we add an employee) the field Number of Employees.
How to do it?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander, 2016-10-08
@EgoRusMarch

CREATE TRIGGER sampleTrigger
    ON database1.dbo.[СОТРУДНИКИ]
    FOR DELETE
AS
    UPDATE database1.dbo.[ОТДЕЛЫ]
    SET [КОЛСОТР] -= 1
    WHERE [КОДОТДЕЛА] = (SELECT deleted.[КОДОТДЕЛА] FROM deleted)
GO

This is for an example. But you need to write for all cases, including when there are more than one deleted records
https://msdn.microsoft.com/ru-ru/library/ms191300.aspx

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question