Thoughts on triggers and triggers

Thoughts on triggers and triggers

After reading Zhou's blog, I saw an article about the trigger. When you recharge your account, you need to insert a record in the t_reCharge table and update the t_card table to ensure data consistency. we didn't think much at the time. We didn't expect the trigger to be written into multiple SQL statements. To avoid errors, we used transactions or stored procedures. If we remember correctly, we put them in the stored procedures. Her implementation is as follows:

 

"The recharge table ensures that the latest recharge record is extracted. The card table ensures that the updated record meets the requirement that the card number is equal to the recharge card number. "

 

<spanstyle="font-family:FangSong_GB2312;">CREATETRIGGER[dbo].[CardCash]  on [dbo].[T_Recharge]  for Insert AS  BEGIN  UPDATE T_Card set sCash  =sCash +(select top 1 rAdd  fromT_Recharge order by rNo desc) WhereT_Card .cNo in (select top 1 cNo  fromT_Recharge order by rNo desc )    END</span>

 

The original Article link is as follows:

Burden reduction for layer D-use of stored procedures and triggers

 

I have two questions:

1. SQL statements are hard-wired. Can I pass parameters?

2. If two cards are recharged at the same time, will the latest t_reCharge record found be from another card? In other words, what are the prefix and suffix of update and insert? In fact, I never figured out which record should I insert when two cards recharge at the same time?

3. If the trigger in step 2 is a transaction, the problem will not occur if it is both executed or not executed. Otherwise, do you want to add a transaction?

 

Next, I learned how the trigger works.

The INSERT, UPDATE, and DELETE operations that change table data can be divided into three types: INSERT trigger, UPDATE trigger, and DELETE trigger.

1. INSERT trigger

When you try to INSERT a record to a table, the INSERT trigger (if any) is automatically executed. In this case, the system automatically creates an inserted Table and adds new records to the trigger table and inserted Table. The trigger can check the inserted Table to determine whether to execute the trigger action and how to execute the trigger action.

2. DELETE trigger

When you try to DELETE information from a table, the DELETE trigger is triggered. At this time, the system automatically creates a deleted table and the deleted rows are placed in this special table. The row to be deleted will no longer exist in the trigger table. Therefore, there is no common record between the trigger table and the deleted table.

3. UPDATE trigger

The UPDATE statement can be used as two steps to delete an old record and insert a new record. Therefore, when the UPDATE trigger is triggered, a deleted table and an inserted Table are automatically created. The UPDATE statement moves the original row into the deleted table and inserts the updated row into the inserted Table.

 

Here we are an insert trigger. Why is the trigger used? Use a similar example. We have two tables to record the warehouse receiving and warehouse receiving of goods. t_good_store records the product category and quantity of stock, while t_good_out records the product category and quantity of warehouse picking, when a certain number of products are delivered to a certain category, we should insert the category and number of products in t_good_out, at the same time, we should also use update in t_good_store to update the number of products in the corresponding category of the inventory. At this time, we need to complete two tasks: insert t_good_out and update t_good_store, to avoid data inconsistency caused by only one operation, we can use a trigger to bind a trigger for t_good_store update to the t_good_out insert operation. Of course, this process should be a transaction, so you don't have to worry about inserting the t_good_out table for execution, but the trigger operation bound to this action won't be executed, because the database has designed atomicity.

 

Let's take a look at the above three questions.

1. A trigger is triggered only when it is modified (including insertion and deletion). The purpose is to give us a chance to perform other actions. It does not have the ability to perform other actions when querying data. when I asked whether the trigger has a parameter, I didn't understand this feature at the beginning.

In addition, based on the trigger's working principle, we can access the temporary tables inserted and deleted. What we need to understand is that inserted stores the data after the insert and update operations; deleted stores the data before the delete and update operations.

Update = delete + insert. this reminds us that one page in the DRP system is to modify Item, client, or flowCard. You can delete both pages and insert them, and then prompt that the modification is successful. Otherwise, there will be many items, how can I judge and then modify one by one?

2. some people may say that we can insert two records at the same time, because insert is at the millisecond level, but there are first and last records at whatever level. insert is a rowlevel Lock. If you do not Lock the table, you cannot determine the sequence.

3. I think transactions can be skipped. If the trigger is designed to accommodate other operations between insert and update, it makes no sense.

 

Let me first try it out. Even if it is nothing, we will accept bricks silently and modestly...

Is the trigger easy to use?

The most commonly used trigger is to maintain data integrity. Indeed, most of the things it can implement in the code, but if multiple users are involved in simultaneous operations, writing code in a program is certainly not as convenient as writing code in a trigger.

Take safety stock for example. For example, two users need to produce the same product at the same time. The current inventory is 100, A user needs 80, and B needs 50; before that, all they know is a piece of data, that is, the inventory is 100, and they all think that the inventory can be checked out, but you will find a problem. If they all come out of the warehouse, the inventory quantity is obviously insufficient, at this time, if you write code in the program, you need to judge before the database is released and perform transaction processing. However, if you write code in the trigger, users A and B can release the database (UPDATE inventory), but one user will be prompted that the inventory is insufficient, the error message is returned if the number of warehouse to be exported is greater than the inventory value.

In addition, if multiple systems (such as WEB and desktop systems) use this database at the same time, you only need to write the maintenance code in the trigger, you do not need to write the same code in each system at the same time.

The above is just one aspect of the trigger application, and it can also do some other "aftercare" work, which is waiting for you to explore.

For testing, you can use the query analyzer to directly write statements for testing.

What is the working principle of a trigger?

JK trigger is a circuit unit in a digital circuit trigger.
JK triggers include 0, 1, hold, and flip. Among the various integrated triggers, JK triggers provide the most complete functions. In practical applications, it not only has a strong versatility, but also can flexibly convert other types of triggers. The JK trigger can constitute the D trigger and the T trigger.
1. When CP = 0, the trigger is in a steady state. When the CP value is 0, G3 and G4 are blocked. regardless of the status of J and K, Q3 and Q4 are both 1. On the other hand, G12 and G22 are also blocked by CP, therefore, a trigger consisting of a trigger or a non-gate is in a stable State, so that the output Q and Q remain unchanged.
2. When the CP changes from 0 to 1, the trigger does not flip to prepare for receiving the input signal.
JK trigger circuit diagram:
Set the trigger status to Q = 0 and Q = 1. When CP changes from 0 to 1, there are two signal channels that affect the trigger output status. One is open G12 and G22, which directly affect the trigger output, and the other is open G4 and G3, then G13 and G23 affect the trigger status. The previous channel only goes through level 1 and door, while the other channel goes through level 1 and non-door and Level 1 and door. Obviously, the switch of CP affects the output much faster than the latter. When CP is changed from 0 to 1, the output of G22 is first changed from 0 to 1. In this case, Q is still 0 regardless of the G23 status (that is, regardless of the status of J and K. Q connects the G12 and G13 inputs at the same time, so their output is 0, so that the output Q of G11 is 1, and the trigger status remains unchanged. After the CP changes from 0 to 1, turn on G3 and G4 to prepare for receiving the input signal J and K.
3. When CP is changed from 1 to 0, if the trigger is flipped and the input signal J = 1 and K = 0, Q3 = 0, Q4 = 1, and G13 and G23 are both 0. When the CP drops along, the G22 output is changed from 1 to 0, Q = 1, so that the G13 output is 1, Q = 0, and the trigger is flipped. Although G3, G4, G12, and G22 are blocked after CP is changed to 0, Q3 = Q4 = 1, due to the delay time ratio with non-gate and the door length (guaranteed in the manufacturing process ), therefore, the new States Q3 and Q4 are stable after the trigger is flipped. It can be seen that the trigger triggers the flip when the CP falls along. Once the CP reaches the 0 level, the trigger is blocked and is in the situation analyzed in (1.
In short, the trigger receives information before the CP descent edge, triggers the flip at the descent edge, and the trigger is blocked after the descent edge.