Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mitcho
Starting Member
7 Posts |
 Posted - 2004-09-19 : 21:29:43
|
| Hey everyone,I just signed up today as i am new to sql, and have a little problem i was hoping for some feedback.I have a table called Nurse (see below). Nurse has an attribute called Exp_date which is the expiry date of their license. Their licenses have to be renewed every 2 years so i am trying to write an Assertion to check this (see below). unfortunaltey i am quite new to this and am not sure if it will work. I was wondering if anyone could let me know if it is wrong or if it will work. if it is wrong can i have some suggestions.here is the Nurse table:CREATE TABLE Nurse( Nurse_ssn varchar(12), Lic_no varchar(9), Exp_date date NOT NULL, CONSTRAINT _PK PRIMARY KEY(Nurse_ssn),);here is my assertion to check that the license has been renewed in the last 2 years:CREATE ASSERTION expiry-constraint CHECK(SELECT Exp_dateFROM Nurse((Sysdate - Exp_date)< 2));Thanks for anyone who is willing to help me...regards,mitchell page |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-19 : 21:48:22
|
| Which database are you using?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mitcho
Starting Member
7 Posts |
Posted - 2004-09-20 : 02:31:38
|
| At the moment its generic - do you get me???Not a specific database application; im working from a text book and im not trying to get this to work on a system, im just doing it on paper. its a question im having trouble with.anyone help me with that?regards,mitchell page |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-20 : 13:04:09
|
| Just that you've posted in an sql server forum.Try it in "other topics"and specify that it is generic.What is the constraint trying to do. A constraint will probably stop entries being inserted that violate the constraint (if the database supports that type of constraint) but once it's there will not throw an error when the licence is 2 years old. In sql server to do that you would have to create a scheduled job which checked the table.in sql server your constraint would be something likealter table Nurse add constraint expiry-constraint CHECK (Exp_date < dateadd(yy,-2,getdate())To protect against invalid inserts.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mitcho
Starting Member
7 Posts |
Posted - 2004-09-21 : 00:08:24
|
| Why are Assertions better suited to express rules such as this? Why not just use check constraints? Can anyone tell me why??regards,mitchell page |
|
|
|
|
|
|
|
|
SQL assertion