CS669 Lab 4 Another practical use of a trigger is cross-table validation (that is, the validation needs columns from at least one table external to the table being updated). Create a trigger that blocks a "like" from being inserted if its "liked_on" date is before the post's "created_on" date. Verify the trigger works by inserting two "likes" - one that passes this validation, and one that does not. List out the Likes table after the inserts to show one insert was blocked and the other succeeded. student Worksheet Query Builder CREATE OR REPLACE TRIGGER check_liked_on_trg BEFORE UPDATE OR INSERT ON Likes FOR EACH ROW DECLARE 44 v_created on DATE; SELECT post.created_on INTO v_created_on FROM Post WHERE post.post_id= :NEW.post_id; BEGIN IF :NEW. liked_on < v_created_on THEN RAISE APPLICATION ERROR(-20001, 'You can like a post before the date of post created.'); END IF; END Script Output x | Task completed in 0.463 seconds Trigger CHECK LIKED_ON_TRG compiled Correct query according to trigger SOL student POST Worksheet Query Builder INSERT INTO likes (likes_id, person_id, post_id, liked_on) VALUES (likes_seq. nextval, 4, 5, CAST('01-AUG-2021' AS DATE));| Script Output x 1 row inserted. SOL Aa Incorrect query according to trigger | Task completed in 0.194 seconds SQLQuery1.sql -...PTJBS5\user (53))* X CREATE TRIGGER checklike ON Likes AFTER, INSERT UPDATE AS BEGIN END DECLARE @likedon DATE; SELECT @likedon = INSERTED. liked on from INSERTED; DECLARE @Postcrated_on FROM Post join INSERTED ON INSERTED. post_id-post, post_id); if (@likedon @post) BEGIN END: ROLLBACK; raiserror(error'); STEP 9 To demonstrate cross-table validation, imagine we want to validate the fact that the line price for a line item actually equals the quantity times the item price. The quantity is stored in the Line_item table while the item price is stored in the Item table. We can setup a trigger on the Line_item table to perform this validation using constructs we've already used in prior steps in this lab. Here is the code for such a trigger. Another practical use of a trigger is cross-table validation (that is, the validation needs columns from at least one table external to the table being updated). Create a trigger that blocks a "like" from being inserted if its "liked_on" date is before the post's "created_on" date. Verify the trigger works by inserting two "likes" - one that passes this validation, and one that does not. List out the likes table after the inserts to show one insert was blocked and the other succeeded. CREATE TRIGGER line_price_trg ON Line_item AFTER INSERT, UPDATE AS BEGIN Code: Correct Line Price Validation Trigger DECLARE @v_actual_line_price DECIMAL (12, 2); DECLARE @v_correct_line_price DECIMAL (12,2); SELECT @v_actual_line_price=INSERTED.line_price, FROM Item JOIN INSERTED ON INSERTED.item_id = Item.item_id; END; @v_correct_line_price=INSERTED.item_quantity* Item.price IF @v_actual_line_price @v_correct_line_price BEGIN END; ROLLBACK; RAISERROR ('The line price is not correct. ',14,1); You've seen all of the constructs here individually, but the integration of them needs more explanation. Two variables are declared, one to store the actual line price being inserted or updated, and the other to store the correct line price. Both of these are set by using the SELECT statement to pull from both the Item table as well as the INSERTED pseudo-table. The trigger then uses an if statement to determine if the line price of the new or updated row is correct. If it's not, it rolls back the transaction and raises an error that indicates the line price is not correct. You've seen all of these constructs before, so you're not witnessing just another use case. Let's try it out. We'll try to insert a line item with an invalid line price, as follows.