Trigger for 2 Tables

Question

this is a homework question, just to make it clear.

This is the relational schema:

• PaperInvolvement (paperNr, academicId, paperRole)
• Academic (academicId, acadName, employer)

So (academicID) is the primary key for Academic and (paperNr, academicId) is the primary key for PaperInvolvement table.

Here is the trigger that I am asked to do:

• On PaperInvolvement after insert, update
• On Academic after update
• Prevent any 2 academics who work for the same company are involved in the same paper in the opposite roles.
• Use stored procedure or cover it completely in trigger

There are only 2 roles available in this table, which is Reviewer and Author

This is what I have done so far:

CREATE TRIGGER TR_PaperInvolvement_1
ON PaperInvolvement
AFTER INSERT, UPDATE
AS
IF EXISTS
(
SELECT a.academicId, paperRole, paperNr
FROM
(SELECT academicId
FROM Academic
GROUP BY employer, academicId) AS a
JOIN
(SELECT academicId, paperRole, paperNr
FROM PaperInvolvement

GROUP BY paperNr, academicId, paperRole) AS p_inv
ON a.academicId = p_inv.academicId
WHERE paperRole = 'Author' AND paperRole = 'Reviewer'

)
BEGIN
RAISERROR('Cannot have 2 Academics from the same company to work on
different roles for this paper.',16,1)
ROLLBACK TRANSACTION
END
GO

My question is, based on the requirements (what I have listed on the bullet-lists), is this the correct way to answer the question?

Answer 1

Try this

CREATE TRIGGER TR_PaperInvolvement_Modify
ON PaperInvolvement
AFTER INSERT, UPDATE
AS
begin
    if exists
    (
        select P.paperNr, A.employer
        from PaperInvolvement as P   
            inner join Academic as A on A.academicID = P.academicID 
        where P.paperNr in (select i.paperNr from inserted as i)
        group by P.paperNr, A.employer
        having
            count(case when P.paperRole = 'Author' then 1 end) > 0 and
            count(case when P.paperRole = 'Reviewer' then 1 end) > 0
    )
    begin
        raiserror('Cannot have 2 Academics from the same company to work on different roles for this paper.', 16, 1)
        rollback transaction 
    end
end