Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -----------------------------------------------------------------------
- -- USERS
- -----------------------------------------------------------------------
- CREATE TABLE users
- (
- id NUMBER(10) PRIMARY KEY NOT NULL,
- email CHAR(100) UNIQUE NOT NULL,
- password CHAR(60) NOT NULL,
- first_name CHAR(30) NOT NULL,
- last_name CHAR(30) NOT NULL,
- birth_date DATE NULL,
- gender CHAR(10) NULL,
- joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
- CONSTRAINT gender_check CHECK (UPPER(gender) IN ('M', 'F'))
- );
- -- Auto increment for users.id
- DROP SEQUENCE users_sequence;
- CREATE SEQUENCE users_sequence START WITH 1 INCREMENT BY 1;
- CREATE TRIGGER users_before_insert
- BEFORE INSERT
- ON users REFERENCING NEW AS NEW
- FOR EACH ROW
- BEGIN
- SELECT users_sequence.NEXTVAL INTO :NEW.ID FROM dual;
- END;
- /
- --
- -- POSTS
- --
- CREATE TABLE posts
- (
- id NUMBER(10) PRIMARY KEY NOT NULL,
- user_id NUMBER(10) REFERENCES users(id) NOT NULL,
- created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
- content CHAR(1000) NOT NULL,
- likes NUMBER(10) DEFAULT 0 NOT NULL,
- privacy CHAR(10) DEFAULT 'public' NOT NULL,
- CONSTRAINT fk_posts FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
- );
- -- Auto increment for posts.id
- DROP SEQUENCE posts_sequence;
- CREATE SEQUENCE posts_sequence START WITH 1 INCREMENT BY 1;
- CREATE TRIGGER posts_before_insert
- BEFORE INSERT
- ON posts REFERENCING NEW AS NEW
- FOR EACH ROW
- BEGIN
- SELECT posts_sequence.NEXTVAL INTO :NEW.ID FROM dual;
- END;
- /
- CREATE TRIGGER posts_before_delete
- BEFORE DELETE
- ON posts
- FOR EACH ROW
- BEGIN
- DELETE FROM post_likes WHERE post_id = :OLD.id;
- END;
- /
- --
- -- POST_LIKES
- --
- CREATE TABLE post_likes
- (
- user_id NUMBER(10) NOT NULL REFERENCES users(id),
- post_id NUMBER(10) NOT NULL REFERENCES posts(id),
- PRIMARY KEY (user_id, post_id),
- --CONSTRAINT fk_post_likes1 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
- CONSTRAINT fk_post_likes2 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
- );
- -- Increments posts.likes
- CREATE TRIGGER post_likes_after_insert
- AFTER INSERT
- ON post_likes
- FOR EACH ROW
- DECLARE
- n NUMBER(10);
- BEGIN
- SELECT likes INTO n FROM posts WHERE id = :NEW.post_id;
- UPDATE posts SET likes = n+1 WHERE id = :NEW.post_id;
- END;
- /
- -- Decrements posts.likes
- CREATE TRIGGER post_likes_before_delete
- BEFORE DELETE
- ON post_likes
- FOR EACH ROW
- DECLARE
- n NUMBER(10);
- BEGIN
- SELECT likes INTO n FROM posts WHERE id = :OLD.post_id;
- UPDATE posts SET likes = n-1 WHERE id = :OLD.post_id;
- END;
- /
- -- Full error, EUSEBIU is the username that I'm logged in
- -- ERROR at line 1:
- -- ORA-04091: table EUSEBIU.POSTS is mutating, trigger/function may not see it
- -- ORA-06512: at "EUSEBIU.POST_LIKES_BEFORE_DELETE", line 4
- -- ORA-04088: error during execution of trigger 'EUSEBIU.POST_LIKES_BEFORE_DELETE'
- -- ORA-06512: at "EUSEBIU.POSTS_BEFORE_DELETE", line 2
- -- ORA-04088: error during execution of trigger 'EUSEBIU.POSTS_BEFORE_DELETE'
Advertisement
Add Comment
Please, Sign In to add comment