1

Topic: SQL the Job

To create the trigger forbidding to the user  to add a line in the table a card if in a column the city meets .
Gain! I do not know as ((

2

Re: SQL the Job

Rustam0712;
Any text editor opens and in it the request about the trigger creation, beginning with words Create trigger is typed. Further the written code goes any accessible method on execution to the server and  .

3

Re: SQL the Job

Or show your decision and ask specific questions
Or to you here: to the Students, wishing the help

4

Re: SQL the Job

creat trigger
on THE CARD
after insert
as
begin
if (select a card
from a city
where
End

5

Re: SQL the Job

The grandfather, here my achievements... To understand what condition it can not is necessary
creat trigger
on THE CARD
after insert
as
begin
if (select a card
from a city
where
End

6

Re: SQL the Job

Rustam0712;
Since it is necessary for you "the trigger forbidding to the user to add a line in the table";
That instead of after insert is necessary instead of
To read here and here
Under the first link pay attention to an example And (as on the general direction)
"select a card from a city where" at you [card] is the table, and [city] is a column
Write to begin with simply  deducing 1 if in the table already there is a city of Moscow to read about exists
"Forbidding to the user " - here a little indefinitely, but to begin with to read about current_user

7

Re: SQL the Job

wrote:

Rustam0712;
Since it is necessary for you "the trigger forbidding to the user to add a line in the table";
That instead of after insert is necessary instead of

What for? What for to mold instead of there where it it is completely not necessary?

8

Re: SQL the Job

Mind wrote:

it is passed...
What for? What for to mold instead of there where it it is completely not necessary?

what the person studies the tool of triggers and received quite to itself the specific job for fastening of the transited material - to your head does not come?

9

Re: SQL the Job

  - Eh wrote:

is passed...
What the person studies the tool of triggers and received quite to itself the specific job for fastening of the transited material - to your head does not come?

triggers INSTEAD OF wrote:

Principal advantage of triggers INSTEAD OF that they allow to support updates for such representations which it is impossible to update. That representation on the basis of several underlying tables supported an insertion, update and removal of the data in several tables, it is necessary to use trigger INSTEAD OF. Other advantage of trigger INSTEAD OF consists that it provides logic of the code at whom it is possible to reject one parts of a packet and to accept others.
Trigger INSTEAD OF can fulfill following actions:
To pass packet parts;
Not to process a part of a packet and to save a line with malfunctions in log;
To fulfill alternative action in case of an error.

What exactly from the aforesaid was necessary for the person?
Next time when me ask as to update field A value B where a C field = X I will tell that it is necessary to use the cursor, and that,  to itself the job, let schoolboys .

10

Re: SQL the Job

Mind;
The sense to rest and  pieces docks?
Specific job  in the first post, result the variant of the trigger, we consider.

11

Re: SQL the Job

wrote:

Mind;
The sense to rest and  pieces docks?
Specific job  in the first post, result the variant of the trigger, we consider.

Consider:

create trigger t_Table_BI on t_TABLE
after insert
AS
BEGIN
if (suser_name () = ' Ivan ' and exists (select 1 from inserted i i. City like ' % % '))
begin
raiserror (' Vanja to you cannot be interposed. ', 1000, 1, ' aa ')
rollback tran
return
end
END

12

Re: SQL the Job

Jaffar;
I.e. you suggest to interpose the data into the table (though the job not to interpose) at first
Then to make a certain check
And if check did not transit to roll away transaction
I correctly understood you? smile
Question and if artful Petja made at this time  with nolock?

13

Re: SQL the Job

wrote:

a question and if artful Petja made at this time  with nolock?

That artful Petja  for it is told that READ UNCOMMITTED can lead to dirty reading, i.e. reading of lines which in basis was not and will not be (in  a type, clear business).
To fence instead of the trigger for the insertion prohibition, from my point of view, unduly, it is enough to make posthandling with rollback at violation of conditions.
I would reflect about instead of only in the event that in setting would be explicitly written that the erratic data should not be accessible to other transactions reading in READ UNCOMMITTED, only then preprocessing in instead of the trigger would be required.

14

Re: SQL the Job

Minamoto wrote:

it is passed...
That artful Petja  for it is told that READ UNCOMMITTED can lead to dirty reading, i.e. reading of lines which in basis was not and will not be (in  a type, clear business).
To fence instead of the trigger for the insertion prohibition, from my point of view, unduly, it is enough to make posthandling with rollback at violation of conditions.
I would reflect about instead of only in the event that in setting would be explicitly written that the erratic data should not be accessible to other transactions reading in READ UNCOMMITTED, only then preprocessing in instead of the trigger would be required.

To interpose, then to think and whether it was possible to interpose, it yes the decision, the rights by the way too are in sql. Well and a variant what interposes it can 1 records, and we will roll away then?

15

Re: SQL the Job

Minamoto;
To create the trigger, forbidding to the user  to add a line in the table a card if in a column the city meets .
Given trigger AFTER does not forbid an insertion, it rolls away transaction. Formally the job is not fulfilled,  the insertion has been produced.

16

Re: SQL the Job

argue wrote:

Minamoto;
To create the trigger, forbidding to the user  to add a line in the table a card if in a column the city meets .
Given trigger AFTER does not forbid an insertion, it rolls away transaction. Formally the job is not fulfilled,  the insertion has been produced.

I would tell that it to the discretion of the teacher who will check job performance.
One can tell that the insertion is completed when operation of change of pages and then it is required instead of insert is led. Another can tell that the insertion is completed at end of the transaction containing paste operation and then it is enough after insert.
The third generally itself does not understand distinction in triggers, and any implementation approaches it.
From the point of view of implementation in real operation I saw implementations with after insert is more often - the prohibition of an insertion in instead of never saw.
What teacher will check operation (and what implementation to select) - knows only the HARDWARE, therefore to argue, really, there is nothing.

17

Re: SQL the Job

Minamoto wrote:

to Fence instead of the trigger for the insertion prohibition, from my point of view, unduly, it is enough to make posthandling with rollback at violation of conditions.

one more young and hot in the company to Jaffar... smile
And matter is not in the teacher, and in the logician
However, if to you tonsils  * ", who I such, that are closer"...

18

Re: SQL the Job

wrote:

it is passed...
One more young and hot in the company to Jaffar... smile
And matter is not in the teacher, and in the logician
However, if to you tonsils  * ", who I such, that are closer"...

And me then write down in young and hot (though it, unfortunately, for a long time already not so)
In the given task it is absolutely not necessary instead the trigger.
Besides, that on them there are additional restrictions, and that the code will be longer, he should be rewritten at adding/removal of fields in the table.

19

Re: SQL the Job

wrote:

it is passed...
One more young and hot in the company to Jaffar... smile
And matter is not in the teacher, and in the logician
However, if to you tonsils  * ", who I such, that are closer"...

If I young and hot, you, most likely, old and cold? smile
I normally start to work strictly on the formal logic when it is necessary to spoil interaction with the certain person - "both wrote, and made, and that does not work - so it you so wrote". When it is necessary to make, that worked, it is necessary to clarify, what result as a result should turn out, and implement, proceeding from demanded result, realizing (whenever possible) restrictions and singularities of the selected approach.

20

Re: SQL the Job

wrote:

it is passed...
One more young and hot in the company to Jaffar... smile
And matter is not in the teacher, and in the logician
However, if to you tonsils  * ", who I such, that are closer"...

to Mould on each table INSTEAD OF it and there are tonsils through *.
With that that the trigger is a crutch I hope all agree? And INSTEAD OF the trigger is a crutch in a square. It is applied normally when you than the standard insertion in the table does not arrange that, and it is impossible to change the code, for example source codes of application are not present. For the banal prohibition of an insertion would approach normal CHECK Constraints, but the teacher invented the job for examination of triggers.

21

Re: SQL the Job

Mind wrote:

it is passed...
To mold on each table INSTEAD OF it and there are tonsils through *.
With that that the trigger is a crutch I hope all agree? And INSTEAD OF the trigger is a crutch in a square. It is applied normally when you than the standard insertion in the table does not arrange that, and it is impossible to change the code, for example source codes of application are not present. For the banal prohibition of an insertion would approach normal CHECK Constraints, but the teacher invented the job for examination of triggers.

a sixth
About sectarians were tightened

22

Re: SQL the Job

argue wrote:

Given trigger AFTER does not forbid an insertion, it rolls away transaction. Formally the job is not fulfilled,  the insertion has been produced.

So the person not understanding  operations of transactions can argue only.

Minamoto wrote:

it is passed...
I would tell that it to the discretion of the teacher who will check job performance.
What teacher will check operation (and what implementation to select) - knows only the HARDWARE, therefore to argue, really, there is nothing.

and still the job speaks nothing about a case if the user interposes at once two lines where only one does not satisfy to a condition what should happen to the second? What trigger also will depend on the answer to this question as a matter of fact to use.

23

Re: SQL the Job

argue wrote:

Minamoto;
To create the trigger, forbidding to the user  to add a line in the table a card, if in a column the city meets .
Given trigger AFTER does not forbid an insertion, it rolls away transaction. Formally the job is not fulfilled,  the insertion has been produced.

In a column - what? Interposed or such record already is in the table?
Teachers - woodpeckers

24

Re: SQL the Job

All;
On what argue that - the trigger is, labs in University on Databases - it is enough of it for delivery.
Most likely even the teacher does not know a difference between  and . - and you here will be measured by accuracy....