I need some help about triggers. I’m currently developing a platform and a database in order to manage exams at my university. Here is my problem:
I have 1 supertype table, which contains all the persons registered on the platform. I have to be able to make the distinction for each person between the functions “Candidate” and “Examiner”. So I have my 2 subtype tables, one for all the candidates and one for all the examiners. To achieve that, I’m using insert triggers.
In addition, a person can be both Candidate and Examiner, but not at the same time. So after updating the supertype table, I also need a trigger to be able to delete a specific row from one of the two-subtype table and insert the user information on the other.
Here is a simplified design of these 3 tables:
My INSERT trigger :
ALTER TRIGGER [dbo].[role_insert]
ON [dbo].[alemp_persons]
FOR INSERT
AS
DECLARE @random_number int
SELECT @random_number = CAST(CAST(rand() as binary(2)) as int)
BEGIN
INSERT INTO dbo.alemp_candidates
(
id_person, random_number
)
SELECT id_person, @ random_number
FROM INSERTED
WHERE function='Candidate'
INSERT INTO dbo.alemp_examiners
(
id_person
)
SELECT id_person
FROM INSERTED
Where function='Examiner'
END
GO
My UPDATE trigger :
ALTER TRIGGER [dbo].[role_update] ON [dbo].[alemp_persons]
AFTER UPDATE
AS
DECLARE @id_person int
DECLARE @newFunction int SELECT @newFunction=function FROM inserted
DECLARE @random_number int SELECT @ random_number = CAST(CAST(rand() as binary(2)) as int)
IF @newFunction = 'Candidate'
BEGIN
DELETE
FROM dbo.alemp_examiners
WHERE id_person=@id_person
END
BEGIN
SET IDENTITY_INSERT dbo.alemp_candidates ON;
INSERT INTO dbo.alemp_candidates
(
id_person, random_number
)
SELECT @id_person, random_number
SET IDENTITY_INSERT dbo.alemp_candidates OFF;
END
IF @newFunction = 'Examiner'
BEGIN
DELETE
FROM dbo.alemp_candidates
WHERE id_person=@id_person
END
BEGIN
SET IDENTITY_INSERT dbo.alemp_examiners ON;
INSERT INTO dbo.alemp_examiners
(
id_person
)
SELECT @id_person
SET IDENTITY_INSERT dbo.alemp_examiners Off;
END
GO
As I said above, my INSERT trigger works as I want. However when I want to update the function of one person, I got an error :
Explicit value must be specified for identity column either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.