1

Topic: The trigger

Help please) I need to write the trigger which forbids change of some values.
I find these values here so:
(select fname from Faculty
natural join (
select fid from (
select fid, count (fid) as c from Relationship
group by fid)
where c> 0)));
Then I do the trigger:
CREATE TRIGGER error BEFORE UPDATE OF fname
ON Faculty
BEGIN
SELECT RAISE (FAIL, "There was an error, at this faculty students" study) FROM Faculty
WHERE (fname = (select fname from Faculty
natural join (
select fid from (
select fid, count (fid) as c from Relationship
group by fid)
where c> 0)));
END;
But this trigger  forbade to update fname in the table, and the prohibition only on the necessary is necessary to me.
How it to make? Help please)

2

Re: The trigger

1998 wrote:

Help please) I need to write the trigger which forbids change of some values.
I find these values here so:
(select fname from Faculty
natural join (
select fid from (
select fid, count (fid) as c from Relationship
group by fid)
where c> 0)));
Then I do the trigger:
CREATE TRIGGER error BEFORE UPDATE OF fname
ON Faculty
BEGIN
SELECT RAISE (FAIL, "There was an error, at this faculty students" study) FROM Faculty
WHERE (fname = (select fname from Faculty
natural join (
select fid from (
select fid, count (fid) as c from Relationship
group by fid)
where c> 0)));
END;
But the trigger  forbade to update this fname in the table, and the prohibition only on necessary is necessary to me.
How it to make? Help please)

The trigger of the rights, to it - 5, to you - 2

3

Re: The trigger

Rolg Hupin, you know as to correct?

4

Re: The trigger

1998 wrote:

Rolg Hupin, you know as to correct?

What for so

select fid from
(
select fid, count (fid) as c from Relationship
group by fid
)
where c> 0

Instead of so

select fid from
(
select fid, count (fid) as c from Relationship
group by fid
having count (fid)> 0
)

And so

select fname from Faculty f inner join
(
select fid, count (fid) as c from Relationship
group by fid
having count (fid)> 0
) t on t.fid=f.fid

"But this trigger  forbade to update fname in the table, and the prohibition only on the necessary is necessary to me.
How it to make?"
He forbade to update all which get to your sampling.
What is "necessary" - you know only.

5

Re: The trigger

Rolg Hupin, well a problem not in it)

6

Re: The trigger

Rolg Hupin, at me is 5 records, this  deduces 4, they to me cannot be renamed, and 5 which does not enter into this sampling, it is necessary to make so that it was possible to rename

7

Re: The trigger

1998 wrote:

Rolg Hupin, at me is 5 records, this  deduces 4, they to me cannot be renamed, and 5 which does not enter into this sampling, it is necessary to make so that it was possible to rename

I wrote already
"What is" necessary "- you know only."
Result particulars, structure, an example

8

Re: The trigger

Rolg Hupin, at me is such tables: faculty, students and communication. Communication -  the student,  faculty. I need to be forbidden change of a name of faculty on which one student studies at least. Faculties on which there are students I I deduce here that .

9

Re: The trigger

1998;
http://www.sql.ru/forum/983207/kak-pros … s-zaprosom
Point number 2

10

Re: The trigger

White Owl;
CREATE TABLE "Faculty" (` fID ` NUMBER NOT NULL, ` fName ` TEXT, ` initiation ` DATE, ` fullName ` TEXT, PRIMARY KEY (` fID `))
CREATE TABLE "Student" (` sID ` NUMBER NOT NULL, ` Name ` VARCHAR, ` lName ` VARCHAR, ` sName ` VARCHAR, ` Course ` INTEGER CHECK (1 <= Course <= 6), ` Birthdates ` VARCHAR, ` Telephone ` VARCHAR, ` GPA ` INTEGER, ` entry ` DATE, PRIMARY KEY (` sID `))
CREATE TABLE ` Relationship ` (` sID ` NUMBER, ` fID ` NUMBER, FOREIGN KEY (` sID `) REFERENCES ` Student ` (` sID `) on delete cascade)
I attached .

11

Re: The trigger

1998 wrote:

White Owl;
CREATE TABLE "Faculty" (` fID ` NUMBER NOT NULL, ` fName ` TEXT, ` initiation ` DATE, ` fullName ` TEXT, PRIMARY KEY (` fID `))
CREATE TABLE "Student" (` sID ` NUMBER NOT NULL, ` Name ` VARCHAR, ` lName ` VARCHAR, ` sName ` VARCHAR, ` Course ` INTEGER CHECK (1 <= Course <= 6), ` Birthdates ` VARCHAR, ` Telephone ` VARCHAR, ` GPA ` INTEGER, ` entry ` DATE, PRIMARY KEY (` sID `))
CREATE TABLE ` Relationship ` (` sID ` NUMBER, ` fID ` NUMBER, FOREIGN KEY (` sID `) REFERENCES ` Student ` (` sID `) on delete cascade)
I attached .

do not forget button SRC. It directly over a field of the text at a forum.
Result a script of filling of these three tables the data. Show what was specific records it is necessary to touch the trigger and what it is not necessary. Also describe words than these two groups of records differ.

1998 wrote:

I have such tables: faculty, students and communication. Communication -  the student,  faculty. I need to be forbidden change of a name of faculty on which one student studies at least. Faculties on which there are students I I deduce here that .

You are assured that all three tables for this task are necessary to you?
And generally, on the code:
- If you do foreign key in Relationship on Student why you do not do on Faculty?
- Do not frame names of tables and fields neither in inverted commas, nor in apostrophes - it is not necessary.
- It is not necessary to do triple comparing (1 <= Course <= 6) is as will not work.
- In table Student, three fields: Name, lName and sName - my crystal full-sphere cannot prompt what data are expected in this field.
- In table Student, field Birthdates is it is supposed that the student was born three-four times? Can then in the separate table it to select?
- In table Student, field Birthdates it VARCHAR, and entry DATE. Why? And by the way, you know that in sqlite there is no type DATE?
- Why half of fields begins with uppercase letter, and half from the capital?

12

Re: The trigger

White Owl;
1) there is no exterior key because so the job in laboratory has been delivered.
2) I did not frame, when created. In DB SQLite it is possible to copy CREATE any table
3) at  this check worked)
4) the Name the Surname the Patronymic
5) the table is created for all students,  at me cannot be 2 identical students, I know that the table is not absolutely correctly constructed because there is field Course, and one student can be at 2 faculties, on different courses. I will throw this field in table Relationships
Remaining - errors in training activity, I . I accept and I understand criticism, but not the correct types and titles of fields, do not influence creation of my trigger) the Question was concerning the trigger and how to superimpose it on some values. You can help me with it?)

13

Re: The trigger

White Owl, and the job sounds so:
To create the trigger which forbids to rename faculty on which students study. I , shows which values it is impossible to change.

14

Re: The trigger

1998 wrote:

White Owl;
1) there is no exterior key because so the job in laboratory has been delivered.
2) I did not frame, when created. In DB SQLite it is possible to copy CREATE any table
3) at  this check worked)
4) the Name the Surname the Patronymic
5) the table is created for all students,  at me cannot be 2 identical students, I know that the table is not absolutely correctly constructed because there is field Course, and one student can be at 2 faculties, on different courses. I will throw this field in table Relationships
Remaining - errors in training activity, I . I accept and I understand criticism, but not the correct types and titles of fields, do not influence creation of my trigger) the Question was concerning the trigger and how to superimpose it on some values. You can help me with it?)

ps
novichek-> the beginner
Not the correct types-> wrong types

15

Re: The trigger

1998 wrote:

White Owl;
1) there is no exterior key because so the job in laboratory has been delivered.

That is the job DEMANDS that there was one foreign key and there was no another?

1998 wrote:

2) I did not frame, when created. In DB SQLite it is possible to copy CREATE any table

Scripts do not create, scripts write. Because then people will read them and "" add a heap of superfluous nonsense which hinders human eyes.

1998 wrote:

3) at  this check worked)

And you try to set Course =-10

1998 wrote:

4) the Name the Surname the Patronymic

is serious? And whence people can learn it? Here present that someone gave you your script and asked it to correct "slightly". How you learn that in the field sName it is necessary to write a patronymic?

1998 wrote:

remaining - errors in training activity, I . I accept and I understand criticism, but not the correct types and titles of fields, do not influence creation of my trigger) the Question was concerning the trigger and how to superimpose it on some values. You can help me with it?)

Influence and it is very strong. The more untidily your code - the more difficult it to understand. And the it is easier to commit errors.

16

Re: The trigger

1998 wrote:

White Owl, and the job sounds so:
To create the trigger which forbids to rename faculty on which students study. I , shows which values it is impossible to change.

I do not know that shows your script. It does not show me anything except "syntax error".
If I correctly understand structure of your basis, whether that to learn there are students at faculty enough to count a line in table Relationship:

select count (*) from Relationship where fID = _

And your check in the trigger will be

where (select count (*) from Relationship where fID = old.fID)> 0

17

Re: The trigger

White Owl;
Thanks big)