cretueusebiu

sqlplus trigger error

Dec 16th, 2014
117
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.96 KB | None | 0 0
  1. -----------------------------------------------------------------------
  2. --  USERS
  3. -----------------------------------------------------------------------
  4. CREATE TABLE users
  5. (
  6.     id NUMBER(10) PRIMARY KEY NOT NULL,
  7.     email CHAR(100) UNIQUE NOT NULL,
  8.     password CHAR(60) NOT NULL,
  9.     first_name CHAR(30) NOT NULL,
  10.     last_name CHAR(30) NOT NULL,
  11.     birth_date DATE NULL,
  12.     gender CHAR(10) NULL,
  13.     joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  14.     CONSTRAINT gender_check CHECK (UPPER(gender) IN ('M', 'F'))
  15. );
  16.  
  17. -- Auto increment for users.id
  18. DROP SEQUENCE users_sequence;
  19. CREATE SEQUENCE users_sequence START WITH 1 INCREMENT BY 1;
  20. CREATE TRIGGER users_before_insert
  21. BEFORE INSERT
  22.     ON users REFERENCING NEW AS NEW
  23.     FOR EACH ROW
  24. BEGIN
  25.     SELECT users_sequence.NEXTVAL INTO :NEW.ID FROM dual;
  26. END;
  27. /
  28.  
  29. --
  30. -- POSTS
  31. --
  32. CREATE TABLE posts
  33. (
  34.     id NUMBER(10) PRIMARY KEY NOT NULL,
  35.     user_id NUMBER(10) REFERENCES users(id) NOT NULL,
  36.     created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  37.     content CHAR(1000) NOT NULL,
  38.     likes NUMBER(10) DEFAULT 0 NOT NULL,
  39.     privacy CHAR(10) DEFAULT 'public' NOT NULL,
  40.     CONSTRAINT fk_posts FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  41. );
  42.  
  43. -- Auto increment for posts.id
  44. DROP SEQUENCE posts_sequence;
  45. CREATE SEQUENCE posts_sequence START WITH 1 INCREMENT BY 1;
  46. CREATE TRIGGER posts_before_insert
  47. BEFORE INSERT
  48.     ON posts REFERENCING NEW AS NEW
  49.     FOR EACH ROW
  50. BEGIN
  51.     SELECT posts_sequence.NEXTVAL INTO :NEW.ID FROM dual;
  52. END;
  53. /
  54.  
  55. CREATE TRIGGER posts_before_delete
  56. BEFORE DELETE
  57.     ON posts
  58.     FOR EACH ROW
  59. BEGIN
  60.     DELETE FROM post_likes WHERE post_id = :OLD.id;
  61. END;
  62. /
  63.  
  64. --
  65. -- POST_LIKES
  66. --
  67. CREATE TABLE post_likes
  68. (
  69.     user_id NUMBER(10) NOT NULL REFERENCES users(id),
  70.     post_id NUMBER(10) NOT NULL REFERENCES posts(id),
  71.     PRIMARY KEY (user_id, post_id),
  72.     --CONSTRAINT fk_post_likes1 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  73.     CONSTRAINT fk_post_likes2 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  74. );
  75.  
  76. -- Increments posts.likes  
  77. CREATE TRIGGER post_likes_after_insert
  78. AFTER INSERT
  79.     ON post_likes
  80.     FOR EACH ROW
  81.  
  82. DECLARE
  83.    n NUMBER(10);
  84. BEGIN
  85.     SELECT likes INTO n FROM posts WHERE id = :NEW.post_id;
  86.     UPDATE posts SET likes = n+1 WHERE id = :NEW.post_id;
  87. END;
  88. /
  89.  
  90. -- Decrements posts.likes
  91. CREATE TRIGGER post_likes_before_delete
  92. BEFORE DELETE
  93.     ON post_likes
  94.     FOR EACH ROW
  95.  
  96. DECLARE
  97.    n NUMBER(10);
  98. BEGIN
  99.     SELECT likes INTO n FROM posts WHERE id = :OLD.post_id;
  100.     UPDATE posts SET likes = n-1 WHERE id = :OLD.post_id;
  101. END;
  102. /
  103.  
  104. -- Full error, EUSEBIU is the username that I'm logged in
  105. -- ERROR at line 1:
  106. -- ORA-04091: table EUSEBIU.POSTS is mutating, trigger/function may not see it
  107. -- ORA-06512: at "EUSEBIU.POST_LIKES_BEFORE_DELETE", line 4
  108. -- ORA-04088: error during execution of trigger 'EUSEBIU.POST_LIKES_BEFORE_DELETE'
  109. -- ORA-06512: at "EUSEBIU.POSTS_BEFORE_DELETE", line 2
  110. -- ORA-04088: error during execution of trigger 'EUSEBIU.POSTS_BEFORE_DELETE'
Advertisement
Add Comment
Please, Sign In to add comment