"SQL" Constraints and Triggers 2

Source: Internet
Author: User

3. Modifying constraints3.1 giving the constraint a name

Name in the following format:

 gender char  (1  ) constraint   Noandro  check  (Gender in  ( " f   ", "  m   ) 
CONSTRAINT Righttitle     CHECK = ' F ' or  not  like ' ms.% ')
3.2 Modifying constraints on a table

① checking time to modify constraints



② with the ALTER TABLE statement

Delete Constraint drop


Adding constraints Add (note that constraints must be added only when the entire table is adhered to)

ALTER TABLEMoviestarADD CONSTRAINTNameiskeyPRIMARY KEY(name);ALTER TABLEMoviestarADD CONSTRAINTNoandroCHECK(Genderinch('F','M'));ALTER TABLEMoviestarADD CONSTRAINTRighttitleCHECK(Gender= 'F' ORName not  like 'ms.%');

Note: SQL does not remember the constraints that have been removed, so if you want to add a previously deleted constraint, you must write it again.

Iv. assertions

1. Creating assertions

CREATE Assertion assert name CHECK (condition)

2. Using assertions

CREATE CHECK     (notEXISTS        (SELECT  studio.name         from  Studio, movieexec         WHERE=and<1000000         )    );

The above assertion does not allow managers with funds less than 1000000.

CREATE CHECK (10000>=all    (SELECTSUMfromGROUP   by Studioname));

The above assertion does not allow the film company's total movie length to exceed 10,000 minutes.

If this statement is written with a check constraint, it is

CHECK 10000 >=  All     (SELECTSUMfromGROUP by Studioname);

The difference between assertions and check:

Check is only valid for insertions and modifications, but does not check for deletions, and in some cases causes constraints to not be established.

The assertion will be checked when any of the mentioned relationships change, ensuring that conditions are established.

3. Delete assertions

DROP Assertion Assertion Name

V. Triggers

When a particular event occurs, the trigger is activated. If the state satisfies the condition in the trigger, the action connected by the trigger is executed.


1. Not allowed to reduce the net assets of film producers

CREATE TRIGGERNetworthtriggerafterUPDATE  ofNetWorth onmovieexecreferencing Old ROW asoldtuple, NEW ROW asNewtuple forEach ROW when(Oldtuple.networth>Newtuple.networth)UPDATEmovieexecSETNetWorth=Oldtuple.networthWHEREcert#=newtuple.cert#;

2. Prevent filmmakers ' average assets from falling to 500000. To set a trigger for inserts, deletions, and modifications, respectively. Listed here are the modified:

CREATE TRIGGERAvgnetworthtriggerafterUPDATE  ofNetWorth onmovieexecreferencing OldTABLE  asOldstuff, NEWTABLE  asNewstuff forEach STATEMENT when(500000 >(SELECT AVG(NetWorth) frommovieexec))BEGIN    DELETE  frommovieexecWHERE(Name, address, cert#, NetWorth)inchNewstuff; INSERT  intoMovieexec (SELECT *  fromoldstuff);END;

3. Prevent the primary key from being empty as part of the primary key in year. Insert a default value of 1915 before inserting if you find that year is empty

CREATE TRIGGERFixyeartriggerbeforeINSERT  onmoviesreferencing NEW ROW asNewRow NEWTABLE  asNewstuff forEach ROW whenNewRow. Year  is NULLUPDATENewstuffSET  Year = 1915;

The relevant options are described:

After/before: Decide whether the test will occur before or after the event

Events that can be triggered:update\insert\delete

of Properties : Optional for UPDATE, if selected, triggers are activated only if the listed properties change.

When statement : Optional, if selected, the condition is true to execute the following statement, otherwise you must execute the following statement.

Multiple execution actions: by BEGIN, END

Old row as/new ROW as/old table as/newtable as: tuples and tables before and after referencing table modifications

For each row/for STATEMENT: Row-level trigger, statement-level trigger. If the entire table is updated, the row-level triggers are executed one at a time for the modified tuple, and the statement-level trigger is executed only once. Statement-level triggers cannot reference new and old tuples, but can refer to old and new tables, and row-level triggers can be referenced.

Insert tuple no old value, delete tuple no new value

"SQL" Constraints and Triggers 2

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.