Home » SQL & PL/SQL » SQL & PL/SQL » Check Constraint using Trigger (Oracle 10)
Check Constraint using Trigger [message #355932] Tue, 28 October 2008 11:01 Go to next message
dizzyegg
Messages: 1
Registered: October 2008
Junior Member
Hi,

I am trying to create a kind of check constraint but will have to be a trigger because i need to check from another table:
CREATE TABLE MEMBER
	(MEMBERID NUMBER(5) NOT NULL,
	SNAME VARCHAR2(30),
	FNAME VARCHAR2(20),
	DATE_OF_BIRTH DATE,
	ADDRESS_LINE_1 VARCHAR2(70),
	ADDRESS_LINE_2 VARCHAR2(70),
	CITY VARCHAR2(70),
	COUNTY VARCHAR2(20),
	POST_CODE VARCHAR2(10),
	HOME_TEL VARCHAR2(14),
	MOBILE_TEL VARCHAR2(14),
	EMAIL VARCHAR2(50),
	DATE_JOINED DATE)

CREATE TABLE TITLE
	(TITLEID NUMBER(5) NOT NULL,
	TITLE VARCHAR2(120),
	BARCODE VARCHAR2(20),
	DIRECTOR VARCHAR2(70),
	ACTOR VARCHAR2(300),
	GENRE VARCHAR2(50),
	RELEASE_DATE DATE,
	RENT_PRICE NUMBER(4,2))

CREATE TABLE DVD
	(DVDID NUMBER(7) NOT NULL,
	PURCHASED_DATE DATE,
	TITLEID NUMBER(5),
	BRANCHID NUMBER(3) NOT NULL)

CREATE TABLE RENTAL 
	(RENTALID NUMBER(8) NOT NULL,
	DATE_OUT DATE,
	DATE_IN DATE,
	HIRE_COST NUMBER(4,2),
	RETURNED VARCHAR2(1),
	MEMBERID NUMBER(5) NOT NULL,
	DVDID NUMBER(7) NOT NULL)

I need a trigger to check that the member is over 18 if a new record is being created in the rental table and the title genre is XXX.

I really am a bit clueless about this and have spent the last 2 hours reading up to no avail, so any helps in the right direction would be more than helpful.

thanks

[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Tue, 28 October 2008 11:23] by Moderator

Report message to a moderator

Re: Check Constraint using Trigger [message #355945 is a reply to message #355932] Tue, 28 October 2008 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just select the member table in your trigger.
Where is your problem?
Post what you already tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Check Constraint using Trigger [message #356378 is a reply to message #355945] Thu, 30 October 2008 09:48 Go to previous message
fairgame
Messages: 29
Registered: October 2008
Junior Member
Well, you need to get the Member date of birth from your Member table and the DVDID of the last row inserted to check in the "After Insert" trigger.

How to get the last row inserted??
You have the DATE_IN column, so no worries

[Updated on: Thu, 30 October 2008 09:51]

Report message to a moderator

Previous Topic: find 2 to 3rd character but within a range (merged)
Next Topic: problem with sys_refcursor
Goto Forum:
  


Current Time: Sun Dec 04 22:54:25 CST 2016

Total time taken to generate the page: 0.09821 seconds