The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.
       Demand for: statement-level triggers (after the for statement), after all the data into should be executed only once

Please help: error when the trigger statement level,can not fount "old_act_recv_line"table Demand for: Time is: the complete statement data into execution after the trigger ( Action: only perform a trigger)

 ALTER TRIGGER "AC_sendshipper" after  INSERT
    ORDER 4 ON "DBA"."act_recv_line"
      REFERENCING NEW AS new_act_recv_line old as old_act_recv_line
    FOR EACH STATEMENT   
    BEGIN
        declare @html_result long varchar;
        declare @mailid long varchar;
        declare @name long  varchar;
        declare @trans_no long varchar;
    select Distinct customer.e_mail,customer.short_name,shipper.trans_no into @mailid,@name,@trans_no from
    (select * from new_act_recv_line
    EXCEPT
    select * from old_act_recv_line)a,
     act_recv_line,shipper,customer where
             a.trans_no=act_recv_line.trans_no and a.line_no=act_recv_line.line_no
       and shipper.trans_no=act_recv_line.shipper_no and shipper.customer_id=customer.customer_id;
    END

My suspicion was written by My grammar is wrong! The Statement - level triggers should be how to write, because now I want a trigger to help me finish is: When inserted into ten data, trigger only perform a task, whether need to use a statement-level triggers "for each statement," but I can't find any writing on the Internet, maybe everybody don't trigger alt text


secondy chang sql data for each statement

secondy chang SQL code

ALTER TRIGGER "AC_sendshipper" after INSERT
ON "DBA"."act_recv_line"
 REFERENCING NEW AS new_act_recv_line 
FOR EACH STATEMENT
BEGIN
    declare @html_result long varchar;
    declare @mailid long varchar;
    declare @name long  varchar;
    declare @trans_no long varchar;
select Distinct customer.e_mail,customer.short_name,shipper.trans_no into @mailid,@name,@trans_no from
dba.shipper,dba.customer,dba.act_recv_line where 
    new_act_recv_line.trans_no=act_recv_line.trans_no and 
    new_act_recv_line.line_no=act_recv_line.line_no and  
         act_recv_line.shipper_no=shipper.trans_no     and 
          customer.customer_id=shipper.customer_id ;

error for : Not found new_act_recv_line


the third time chang


I insert is multiple data but he every insert a line will send an email to me, and I want to inserted after the new data then send the insert's data to me for example:


a_table:

create table a_table (
   trans_no varchar(64),
   part_id varchar(64),

 );

B_table:

create table b_table (
   trans_no varchar(64),
   part_id varchar(64),
);

insert into b_table values ('FH0001', 900);
insert into b_table values ('FH0003', 900);
insert into b_table values ('FH0003', 100);
insert into b_table values ('FH0003', 200);
commit;

test insert data:

insert into a_table values (trans_no,part_id,) select trans_no,part_id from test where trans_no='FH0003')

USES the results of trigger to send an E-mail to me,
FH0003   900
FH0003   100
FH0003   200

So I just thought of using the trigger statement level to deal with this problem, perform all the insert action after send new data to me.


ALTER TRIGGER "a_table" after INSERT order 4
ON "DBA"."act_recv_line"
 REFERENCING NEW AS new_a_table

BEGIN
    declare @html_result long varchar;
     --get result html
select xmlelement(name "html",    
           xmlelement (name "head",    
               xmlelement(name "title", 'shipper_notif'),    
               xmlelement(name "META", xmlattributes('Content-Type' as "http-equiv" , 'text/html;charset=GBK' as "content"))    
        ),    
           (xmlelement (name "body",      
            xmlelement(name "table", xmlattributes('1' as "border"),  
             xmlelement(name "thead",  
                  xmlelement(name "tr",    
               xmlelement(name "th",  'trans_no),    
                 xmlelement(name "th", 'quantity)
              )    
               ),    
                 xmlelement(name "tbody",    
                  xmlagg( xmlelement (name "tr",    
                       xmlelement(name "td", a_table.trans_no),    
                     xmlelement(name "td", a_table.quantity)

                     ))))))) into @html_result from dba.shn where new_a_table.trans_no=a_table.trans_no ;
--send mail
  call xp_startsmtp(  smtp_sender = '1310636398@qq.com', smtp_server='smtp.qq.com', smtp_port='25',  
 timeout=240, smtp_auth_username='1310636398', smtp_auth_password='password');  
     call xp_sendmail( recipient=test@gmail.com,
                        subject='DEAR Recently, we send you product as follows the arrival of the goods, please find the logistics situation, thank you',  
                       "message"=@html_result,content_type = 'text/html');  
     call xp_stopsmtp( );  
END
This question is marked "community wiki".

asked 01 Dec '13, 21:50

mfkpie8's gravatar image

mfkpie8
86404550
accept rate: 10%

wikified 05 Dec '13, 07:57

I would recommend that you try to tell us what you want to achieve, and we may help. That seems more useful than to tell how to avoid a syntax error.

Note, according to your second trigger, you are using a statement-level trigger, so new_act_recv_line is a temporary table and must be listed in the FROM clause.

(03 Dec '13, 03:34) Volker Barth
1

The sqlcode you are getting is -142, which is a correlation name not found error. This is different from a table not found error. It is important to post the error codes as well as the text when you are requesting help.
Looking at your trigger I see you are referencing "new_act_recv_line" in the WHERE clause of your SELECT statement, but you have not included "new_act_recv_line" in the table list of the FROM clause. That is what is causing the error.

(03 Dec '13, 09:54) Jason Hinspe...

Why do you add a completely different question to your current one?

And have you tried to modify the trigger to add "new_act_recv_line" to the SELECT statement's FROM clause?

Don't get me wrong but I don't see that our attempts to help seem to influence your work...:(

(03 Dec '13, 11:59) Volker Barth

thanks for you reply,Because I found my whole code is too messy, so I changer sql code,I hope you can understand

(03 Dec '13, 12:09) mfkpie8
Replies hidden

You are still making the same mistake. You need to put the new_a_table in the FROM clause.

ie. SELECT ... into @html_result FROM dba.shn, new_a_table WHERE ...

(03 Dec '13, 13:20) Jason Hinspe...

For statement-level triggers that also is not very understand what I mean.

Statement level if need to use a cursor variable assignment values out? Or he needle tip I couldn't find it. "new_a_table" table

(04 Dec '13, 10:27) mfkpie8
Replies hidden
2

As others have mentioned, for statement-level triggers, 'new_a_table' will be a temporary table, that is defined for the lifetime of the trigger.

As Jason mentioned, you need to change your FROM clause in the code you posted to SELECT on this table:

 ...
 ))))))) into @html_result from a_table, new_a_table where new_a_table.trans_no=a_table.trans_no;

Here's the entire trigger definition re-posted that should work for your example:

CREATE TRIGGER "check_insert_on_a_table" after INSERT
ON "DBA"."a_table"
REFERENCING NEW AS new_a_table
FOR EACH STATEMENT
BEGIN
    declare @html_result long varchar;
    --get result html
    select xmlelement(name "html",    
           xmlelement (name "head",    
               xmlelement(name "title", 'shipper_notif'),    
               xmlelement(name "META", xmlattributes('Content-Type' as "http-equiv" , 'text/html;charset=GBK' as "content"))    
        ),    
           (xmlelement (name "body",      
            xmlelement(name "table", xmlattributes('1' as "border"),  
             xmlelement(name "thead",  
                  xmlelement(name "tr",    
               xmlelement(name "th",  'trans_no'),    
                 xmlelement(name "th", 'part_id')
              )    
               ),    
                 xmlelement(name "tbody",    
                  xmlagg( xmlelement (name "tr",    
                       xmlelement(name "td", new_a_table.trans_no),    
                     xmlelement(name "td", new_a_table.part_id)
                     ))))))) into @html_result from a_table, new_a_table where new_a_table.trans_no=a_table.trans_no ;
    --send mail
    call xp_startsmtp(  smtp_sender = '1310636398@qq.com', smtp_server='smtp.qq.com', smtp_port='25',  
                      timeout=240, smtp_auth_username='1310636398', smtp_auth_password='password');  
    call xp_sendmail ( recipient=test@gmail.com,
                        subject='DEAR Recently, we send you product as follows the arrival of the goods, please find the logistics situation, thank you',  
                       "message"=@html_result,content_type = 'text/html');  
    call xp_stopsmtp( );  
END

insert into a_table (trans_no,part_id) select b.trans_no, b.part_id from b_table b where b.trans_no='FH0003';
(04 Dec '13, 11:51) Jeff Albion

Now I understand it turned out that he can make a table for values thanks

(04 Dec '13, 12:27) mfkpie8
More comments hidden
showing 5 of 8 show all flat view

From the documentation: An INSERT takes the REFERENCING NEW clause, which represents the inserted row. There is no REFERENCING OLD clause.

The NEW element is containing the data that was just inserted, but there is no OLD data yet. So if the trigger is meant for an update change it accordingly.

permanent link

answered 02 Dec '13, 02:57

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

My suspicion was written by My grammar is wrong! The Statement - level triggers should be how to write, because now I want a trigger to help me finish is: When inserted into ten data, trigger only perform a task, whether need to use a statement-level triggers "for each statement," but I can't find any writing on the Internet, maybe everybody don't trigger

permanent link

answered 02 Dec '13, 22:56

mfkpie8's gravatar image

mfkpie8
86404550
accept rate: 10%

1

Of course many folks do use both row-level and statement-level triggers.

The main differences are:

  • Row-level triggers are fired for each row, and therefore NEW (and OLD) relate to a single row, and therefore you can use something like "where new_act_recv_line.line_no = ..." similar to a comparison to a single variable.

  • Statement-level triggers are fired for each statement and therefore may treat possibly many rows in one run, and therefore NEW (and OLD) relate to temporary tables that define the set of modified rows. In this case, "new_act_recv_line" is a table so you have to list it in the FROM clause of a SELECT statement to access its columns.

(03 Dec '13, 03:32) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×58

question asked: 01 Dec '13, 21:50

question was seen: 1,252 times

last updated: 05 Dec '13, 07:57