Difficult SQL problems in the Forum: 20 (trigger Topic 2)

Among them, "executing other statements will not inspire nested triggers" means that if the statements after the rollback operation continue to be executed, the triggers triggered by subsequent statements will not be executed. For example, the following statement has an update operation on a table, and this table has an update trigger. At this time, the update operation is successful, but the trigger is not executed?

From the insert output below, we can see that the statement after rollback in the trigger can be executed. In addition, the operation to update another table is also successful, and the trigger is triggered.

-- Drop table t1 -- drop table t2create table t1 (id int) create table t2 (id int) insert into t2values (100) gocreate trigger dbo. trigger_t1on t1for insertasrollback; select 'is the statement after rollback. Here, 'Update t2set id = 1; gocreate trigger dbo can be executed. trigger_t2on t2for updateasselect this is the update trigger of t2. Here we can execute 'go -- insert data into t1values (1)/* This is the statement after rollback, here we can execute this is the update trigger of t2. Here we can execute message 3609, level 16, status 1, 3rd rows of transactions in the trigger to end. Batch processing has been aborted. */-- No record for select * from t1select * from t2/* id1 */

I have written A trigger in Table A and inserted it into Table B to insert A record.

USE [test]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[tba_delete]    ON  [dbo].[tba]    AFTER INSERTAS BEGINSET NOCOUNT ON;insert into test.dbo.tbb(title,info) values('title','info');END

Data inserted in Table A is normal. When I change the language:

insert into test.dbo.tbb(id,title,info) values(1,'title','info');

Intentional error. If the statement in the trigger fails, data inserted into Table A cannot be inserted successfully.
Ask your friends a question. How can I record the error information in the trigger? Or where can I check whether an error occurred while executing the trigger and view the error information?

USE [test] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate table [b7-] (title varchar (20), info varchar (20) create table tbb (title varchar (20 ), info varchar (20) -- create a table for storing error information: create table tb_error_message (obj_name nvarchar (30), obj_type nvarchar (15), err_msg nvarchar (100), err_date datetime) godrop trigger tba_deletegocreate TRIGGER [dbo]. [tba_delete] ON [dbo]. [b7-] AFTER INSERTAS BEGINSET NOCOUNT ON ; Begin try -- the length of the data inserted to the title column is simulated here, which exceeds the 20 insert into test defined. dbo. tbb (title, info) values ', 'trigger', error_message (), getdate () end catchENDgoinsert into tbavalues ('11', '11')/* message 3930, level 16, status 1, process tba_delete, the current transaction of Row 3 cannot be committed, and the operation to write log files is not supported. Please roll back the transaction. The statement has been terminated. */-- Query the error message select * from tb_error_message/* obj_nameobj_typeerr_msgerr_datetba_deletetrigger. The string or binary data is truncated. 12:32:16. 750 */

-- First save a transaction point SAVEPOINT xxx begin try -- or call the stored procedure, or directly write the stored procedure of code exec sending information. end try begin catch select 'execution failed' -- once an error occurs, just roll back TO the transaction point above and continue TO execute ROLLBACK [WORK] to savepoint xxxend catch -- other code

How to Implement the data in field B (integer type) of the AA table is the sum of all data records of the cc field (integer type) in the BB table )!
It is required that the record values of field B in Table AA be automatically updated when the data record of table BB is added.

Yes, triggers can meet your needs. In addition, indexes can speed up. Because of this synchronization requirement, we do not need to span servers. Generally, we can consider Database Synchronization (asynchronous), image (synchronous), log transmission (asynchronous) and other technologies. However, if you want to synchronize data from two tables on the same server, it is most appropriate to use a trigger. The feature of the trigger itself is that in the same transaction, as long as your data is successfully inserted, the data in the other table is synchronized successfully. Similarly, if the insertion fails and an error is reported, the data in the other table will not be synchronized successfully. Then there is the efficiency problem. If sum is used every time and your table contains hundreds of millions of data, this sum is definitely time-consuming, so here we use, if you insert a piece of data, you only need to insert the inserted data to the set c = c + value in the update table, which naturally speeds up the process without the issue of concurrency. If you want to further accelerate the speed, you can consider creating an index for table A, because table a certainly does not have fewer records and must define A record using the where condition, then update. At this time, through the index, you can quickly find this record and then update it.