DB2 Trigger问题

tomjacksun

有谁写过 trigger 插入的，我写了一个 怎么不能通过啊 。
着急
这是 从 center 拷贝出来的
- This script has been created using ^ as the termination character.
-- If you wish to execute this script through the CLP, you will need
-- to add the CLP option -td"^" to your command line,  for example:
-- db2 -td"^" -f filename.ddl

CONNECT TO SDDATA^
CREATE TRIGGER DB2ADMIN.TI_CARD_ACCT AFTER  INSERT  ON DB2ADMIN.CARD_ACCT  REFERENCING  NEW AS newamount  NEW_TABLE AS temp_card_acct  FOR EACH ROW  MODE DB2SQL
WHEN ( newamount = 0)
     
      insert  into temp_card_acct(no_card,amt,date_sys)
select no_card,sum(amount) as amt,max(date_sys) as date_sys
        from card_acct
        group by no_card;
   
END^
CONNECT RESET^

vanessaspring

what's the error msg?
if the error msg is:"SQL1024N  A database connection does not exist.  SQLSTATE=08003"
-- did you connect to your db first with the correct db name?
if the error msg is:"DB21007E  End of file reached while reading the command."
--did you execute the query using the command: db2 -td^ -f filename.ddl

tomjacksun

如何将以下语句更改成DB2 Trigger ?  有点难度，对于 trigger 声明的那个n 变量 ，如何使用，请教。 帮忙啊！

select no_card,sum(amount) as amt,max(date_sys) as date_sys
        into #temp_card_acct
        from inserted
        Group by no_card
        if @@error<>0
        begin
                raiserror 99999 'This is an error'
                return --rollback transaction
        end

        update card_info
        set balance=balance+amt
        from #temp_card_acct
        where card_info.no_card=#temp_card_acct.no_card
        if @@error<>0
        begin
                raiserror 99999 'This is an error'
                return --rollback transaction
        end

        update card_info
        set status=inserted.status
        from #temp_card_acct,inserted
        where         #temp_card_acct.no_card=card_info.no_card and
                #temp_card_acct.no_card=inserted.no_card  and
                    #temp_card_acct.date_sys=inserted.date_sys
        if @@error<>0
        begin
                raiserror 99999 'This is an error'
                return --rollback transaction
        end