Shulou(Shulou.com)06/01 Report--

Note: this paper mainly introduces the use of triggers to automatically synchronize the learning of simple sentences to add, delete and change.

If you find any errors or irregularities in the inspection, you are welcome to leave a message and point out that we can learn from each other. Thank you!

Create the table needed for the test and insert the data. In order to facilitate learning and understanding, the column names are all in Chinese.

Create table a (serial number VARCHAR2 (10) primary key not null, name VARCHAR2 (20), age number (3), address VARCHAR2 (40))

Create table b (serial number VARCHAR2 (10) primary key not null, name VARCHAR2 (20), age number (3), address VARCHAR2 (40))

Insert into a values (1) 'Zhang San', 18 'China')

Insert into b values (1) 'Zhang San', 18 'China')

Commit

Trigger automatic synchronization INSERT

Create trigger: automatically synchronously insert into table B when data is inserted into table A

Create trigger inserts

After insert on a-triggered after data is inserted into table a

For each row-row-level trigger

Begin-the action triggered is as follows

Insert into b (serial number, name, age, address)

Values (: new. Serial number,: new. Name,: new. Age, new. Address)

End

Insert test data into table A

Insert into a (serial number, name, age, address) values ('2Zhanjie' Zhan Kangying', '23' Jing' Hubei')

Commit

Query verification shows that the data in tables An and B have been synchronized.

Select * from a

Select * from b

Trigger automatic synchronization DELETE

Create trigger: automatically synchronously delete data from table B when data is deleted from table A

Create trigger deletes

After delete on a-triggered after data is deleted in table a

For each row-row-level trigger

Begin-the triggered action is as follows

Delete from b where serial number =: old. Serial number

End

Delete the data with serial number 2 in table A

Delete a where serial number ='2'

Commit

Query verification shows that the data in tables An and B have been synchronized.

Select * from a

Select * from b

Trigger automatic synchronization UPDATE

Create trigger: automatically synchronize updates to table B when data is updated in table A

Create or replace trigger updates

Before update on a-triggered after updating data in table a

For each row-row-level trigger

Begin-the action triggered is as follows

Update b set serial number =: new. Serial number, name =: new. Name, age =: new. Age, address =: new. Address

Where serial number =: old. Serial number

End

Update the record with serial number 1 in Table An and change Zhang San to Li Si

Update a set name ='Li Si 'where serial number =' 1'

Commit

Query verification shows that the data in tables An and B have been synchronized.

Select * from a

Select * from b

Drop the trigger after the test is complete, otherwise it will affect the following operations.

Drop trigger inserts

Drop trigger deletes

Drop trigger updates

Triggers automatically synchronize INSERT, DELETE, UPDATE

Create trigger: automatically synchronize operations to table B when there are additions, deletions and changes in table A

Create or replace trigger dmls

After update or insert or delete on a-triggered after adding, deleting or changing data in Table a

For each row-row-level trigger

Begin-the triggered action is as follows

If inserting then-if the following insert statement is run at insert time, otherwise run the next judgment

Insert into b (serial number, name, age, address)

Values (: new. Serial number,: new. Name,: new. Age, new. Address)

Elsif deleting then-if you delete, run the following delete statement, otherwise run the next judgment

Delete from b where serial number =: old. Serial number

Else-- run the following update statement

Update b set serial number =: new. Serial number, name =: new. Name, age =: new. Age, address =: new. Address

Where serial number =: old. Serial number

End if

End

Test the addition, deletion and modification of table A

Insert into a (serial number, name, age, address) values ('319,' Obama','58', 'USA')

Delete a where serial number ='1'

Update a set serial number ='1' where serial number ='3'

Commit

Query verification shows that the data in tables An and B have been synchronized.

Select * from a

Select * from b

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.