Commits

Yang Zhang committed 872ef61

add notify trigger

  • Participants
  • Parent commits 3734013

Comments (0)

Files changed (1)

         WHERE new.topicid = t.id AND new.userid=u.id)
     WHERE id=new.userid;
   END;
-
+-- star a topic trigger
 CREATE TRIGGER star_a_topic AFTER INSERT on star_topic
   BEGIN
     UPDATE user SET star_counter =star_counter+1
     WHERE new.userid = id;
     UPDATE topic SET score =score +5 WHERE new.topicid = id AND posterid!=new.userid;
   END;
-
+-- unstar a topic trigger
 CREATE TRIGGER unstar_a_topic AFTER DELETE on star_topic
   BEGIN
     UPDATE user SET star_counter =star_counter-1
     UPDATE topic SET score =score -5 WHERE old.topicid = id AND posterid!=old.userid;
   END;
 
+--notify trigger
+CREATE TRIGGER notify_counter AFTER INSERT on notify_message
+  BEGIN
+    UPDATE user SET notify_message_counter =notify_message_counter+1
+    WHERE new.userid = id;
+  END;
+
+CREATE TRIGGER read_notify_counter AFTER UPDATE on notify_message WHEN
+(
+    SELECT readed FROM notify_message
+        WHERE new.userid = id
+)
+  BEGIN
+    UPDATE user SET unreaded_counter =unreaded_counter-1
+    WHERE new.userid = id;
+  END;
+  
+  
+  
+  
 CREATE TRIGGER insert_topic_update_counter AFTER INSERT on topic
   BEGIN
     UPDATE counter SET topic_number = topic_number +1;