"SQL" Constraints and Triggers 2

3. Modifying constraints3.1 giving the constraint a name

Name in the following format:

CHAR (CONSTRAINTPRIMARYKEY

 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

SET CONSTRAINT Myconstraint DEFERRED;

SET CONSTRAINT Myconstraint IMMEDIATE;

② with the ALTER TABLE statement

Delete Constraint drop

ALTER TABLE DROP CONSTRAINT Nameiskey; ALTER TABLE DROP CONSTRAINT Noandro; ALTER TABLE DROP CONSTRAINT Righttitle;

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.

Example:

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