Create a trigger in Oracle

create or replace trigger biud_scroe_a

before insert or update or deleteon scroe

beginif user not in ('sgl') thenraise_application_error(-20001, 'You do not have permission to modify this table');

end if;

end;Erroe wrote it wrong.

You don't even need triggers for this.

SGL is a user, right?

If other users want to change it, you can't modify it directly without authorization, is it so troublesome to use triggers?

And the user is the user of **?

if user not in('sgl'It's definitely not okay to write like that.

And the trigger can raise application erroe(-20001,'You do not have permission to modify this table');Is it? I don't think I've ever used anything like this.

Above, I hope it will help you.

A trigger is a special kind of stored procedure Below is a detailed description of the trigger.

Oracle triggers.

The syntax for oracle to generate database triggers is as follows:

create [or replace] trigger Trigger name Trigger time Trigger event.

on Table name.

for each row]

PL SQL statement.

where Trigger Name The name of the trigger object is just a name and has no real purpose since the trigger is executed automatically by the database.

Trigger time indicates when the trigger executes and the value is desirable.

before means that the trigger executes before the database action.

After indicates that the departer executes after the database action.

Trigger event Indicates which database actions trigger this trigger.

insert database insert triggers this trigger.

update database modifications trigger this trigger.

This trigger is triggered by a delete database deletion.

Table Name The table on which the database trigger resides.

for each row trigger is executed once for each row of the table, or only once for the entire table if this option is not available.

Example: The following trigger is triggered before updating the table auths in order not to allow the table to be modified over the weekend.

create trigger auth_secure

before insert or update or delete file: Triggered before the entire table is updated.

on auths

beginif(to_char(sysdate dy )=sun

The parameter is a string, and the system automatically truncates if the size exceeds k).

end if;

lishixinzhi/article/program/oracle/201311/17609

。The definition of a trigger means that when a condition is established, the statement defined in your trigger will be automatically executed. Therefore, triggers do not need to be called by humans, and they cannot be called.

Then, the trigger conditions for the trigger are actually set when you define them. It should be noted here that triggers can be divided into statement-level triggers and row-level triggers. For a detailed introduction, you can refer to the information on the Internet, which simply means that sentence-level triggers can be triggered before or after the execution of certain statements.

The row-level trigger is triggered once when the row data in the defined triggered table changes.

Specific examples:1When a statement-level trigger defined in a table is deleted, the program will automatically execute the operation process defined in the trigger. This is the operation of deleting the table, which is the condition for the trigger to be executed.

2.If a row-level trigger is defined in a table, the trigger will be automatically executed when a row of data in the table changes, such as deleting a row of records.

I wonder if this is clear?

The role of triggers is to focus on triggering words, one thing triggering another.

Triggers oracle can be triggered in a DML statement, before or after a DML operation, and on a per-row or statement operation.

2 .Override triggers.

In Oracle, it is not possible to directly manipulate views created from more than two tables. So an alternate trigger is given. It's a way that Oracle 8 is designed to handle view operations.

3.System triggers.

Oracle 8i provides a third type of trigger, called a system trigger. It can be triggered during events in the Oracle database system, such as the startup and shutdown of the Oracle system.

DML triggers are further divided into row-level (for each row) and statement-level, instead of triggers, instead of

System triggers, also known as database-level, are triggered by user events such as startup and shutdown, such as logon and logoff

Table triggers:

There are 2 trigger states (row-level, object-level), 2 trigger times (before, after), and 7 combinations (insert, update, and delete).

Trigger type = 2*2*7 = 28.

There is also a view trigger instead of28+1=29

Broadly speaking, it should be the following three.

1. System triggers.

Triggers when the database is opened or closed.

2. Table-level triggers.

3. Alternate triggers.

That is, the level of the view is triggered.

Trigger conditions: insert, update, and delete

Trigger timing: before, after

Scope of application: row level, table level.

There are 5 types of triggers in Oracle:

1. Row-level trigger: When the DML statement operates on each row of data, the trigger will run;

2. Statement-level triggers: No matter how many rows of data are affected by a DML statement, the trigger caused by it is only executed once;

3. Replace trigger: The trigger is defined on the view, not on the table, and it is used to replace the trigger of the actual statement used;

4. User event trigger: refers to the trigger related to DDL operations or events such as user login and exit from the database. For example, a user logs in to the database or uses the alter statement to modify the table structure.

5. System event trigger: refers to the trigger triggered in the event of the oracle database system, such as the start and shutdown of the oracle instance.

Trigger.

Oracle can trigger on a DML statement, before or after a DML operation, and on a per-row or statement basis.

Override triggers.

In Oracle, it is not possible to directly manipulate views created from more than two tables. So an alternate trigger is given. It's Oracle

8. A processing method specially designed for view operations.

System triggers.

oracle

8i provides a third type of trigger, called a system trigger. It can be triggered during events in the Oracle database system, such as the startup and shutdown of the Oracle system.

There are three types:1

DML Trigger 2

Alternate Trigger 3

System triggers.