title: Trigger in SQL-How to use query inside the loop?
body: <p>I am working with TSQL and my task is to write trigger which will be activated every time after something is inserted in table Role and it checks if number of main roles exceeds 3. If that is the case we have to stop inserting.</p>
  <p>Here are tables given:
  Actor(AID, name)(AID is primary key)</p>
  <p>Film(FID, name, dateRecorded, numberOfMainRoles)(FID is primary key)</p>
  <p>RoleType(RTID, nameOfType)(RTID is primary key)</p>
  <p>Role(FID, AID,RTID)(FID and AID are primary keys and RTID is foreign key)</p>
  <p>Here is my function:</p>
  <pre><code>CREATE TRIGGER addRole
  ON Role
  After INSERT
  AS
  BEGIN
     DECLARE CR1 CURSOR
     LOCAL
     FROM
        SELECT * FROM INSERTED
     DECLARE @FID INT
     DECLARE @AID INT
     DECLARE @RTID INT
     DECLARE @NUMOFROLES INT
     SET @NUMOFROLES=0
     OPEN CR1
     FETCH NEXT FROM CR1
     INTO @FID, @AID, @RTID
     WHILE @@FETCH_STATUS = 0
     BEGIN
       ...
  
     END
     CLOSE CR1
  END
  </code></pre>
  <p>For while loop I have an idea, but don't know how to realize it. So I would like to write following query into it:</p>
  <pre><code>SELECT rt.RTID FROM 
  (SELECT * FROM
  Film f, Role r
  WHERE f.FID = r.FID) AS table,
  RoleType rt
  WHERE table.RTID = rt.RTID AND rt.nameOfType = &quot;main&quot;
  </code></pre>
  <p>And then my idea is to check whether the RTID of inserted Role is the same as some of RTID-s that I got in the query above, and then ncrement @NUMOFROLES.</p>
  <p>Also, I would probably need to check whether the @NUMOFROLES exceeds the attribute numberOfMainRoles in table Film but not sure how to do that.</p>
  <p>Would be very thankful if someone could solve this for me.</p>