oracle - PL/SQL Trigger Error -

oracle - PL/SQL Trigger Error -

problem: there 3 roles. 1) pupil 2) teaching assistant(ta) 3) teacher. pupil , ta same person different role. both pupil , teacher have unique id called unity id. various courses in courses table students can enroll in , ta's can assist in. various topics associated each course of study can nowadays in multiple course. topics nowadays in different table. rules: 1) ta cannot assist in course of study in he/she student. 2) notification has sent both teachers taking courses.

requirement: whenever ta enrolled(populated in "ta" table), have send notification teacher if topics of same user ta , student(cid in course_roster table corresponds topics in topics table) overlaps.

so, have created notification table gets populated when trigger fired. have created trigger have messed because doing pl/sql first time.

i attaching sample info ta table, course_roster table, topics table, , layout of notification table

class="snippet-code-html lang-html prettyprint-override">course_roster table cid unityid csc440fall14 tregan csc440fall14 mfiser csc440fall14 jander csc440fall14 mjones csc540fall14 aneela csc540fall14 mjones csc540fall14 jmick csc540fall14 tregan ta table cid unityid csc440fall14 aneela csc440fall14 jmick csc540fall14 jharla csc541fall14 jmoyer topics table cid tid topic name csc440fall14 100 introduction database design csc440fall14 101 sql:queries, constraints,triggers csc540fall14 500 introduction database design csc540fall14 501 storing data: disks , files csc540fall14 502 primary file organizations csc540fall14 503 tree structures csc541fall14 502 primary file organizations csc541fall14 503 tree structures notification table t_unityid text s_unityid timestamp

class="snippet-code-html lang-html prettyprint-override">create or replace trigger tr_ta after insert or update on ta each row begin declare tid1 dbms_sql.number_table; declare tid2 dbms_sql.number_table; declare tid3 dbms_sql.number_table; declare @unityid varchar2; declare @unityid2 varchar2; select @unityid= unityid inserted select @unityid2=unityid courses courses.cid=inserted.cid select tid1=t.tid topics t,course_roster c t.cid=c.cid , c.unityid=unityid select tid2=t.tid topics t,inserted t.cid=inserted.cid tid3 := tid2 multiset intersect tid1; if tid3.count > 0 insert notification (unityid, notice, ta_unityid) values(@unityid2, 'subject topic of ta matching course of study topics taken.',@unityid) else print 'ta ok register' end

there many error in code had no thought code going. appreciate help go me through. in advance.

sql oracle plsql triggers