douhutongvm382381 2019-07-16 22:02
浏览 82

当用户从Oracle在PHP应用程序中输入重复的主键时,引发应用程序错误

I'm not sure where to start to raise an error in my php app from oracle. I need to raise an error whenever the user (which would be us here) enters a duplicate primary key, foreign key violation or even not null values. The thing is, i'm not sure where to start, do i need to use a trigger here ?

Look at the code below, i think i'm almost there but i'm not sure what to do from now on. This is just the code for my primary key error. Of course, i assume that if i can make this one work, the others error should not be too hard to code after that

create or replace trigger TRG_PRIMARY_KEY_TYPE_ENCAN
    before insert or update on TP2_TYPE_ENCAN

declare

    cursor PRIM_KEY is
    select CODE_TYPE_ENC FROM TP2_TYPE_ENCAN;
    V_CODE_TYPE_ENCAN char(2);

begin
    select CODE_TYPE_ENCAN into V_CODE_TYPE_ENCAN from TP2_TYPE_ENCAN;
    for V_CODE in PR_KEY 
    loop
    if V_CODE = V_CODE_TYPE_ENCAN then
    raise_application_error(-20050, 'Key duplicate');
    end if;

    end loop;

end TRG_PRIMARY_KEY_TYPE_ENCAN;

I am excepting my raise_application_error message to show in my php app to show that there's a 'Key duplicate'.

  • 写回答

1条回答 默认 最新

  • doutuo7609 2019-07-16 23:46
    关注

    At the SQL level you can add constraints when you create the table. You probably won't need the PL/SQL trigger. Search for 'check constraint' e.g. at https://asktom.oracle.com/

    Then in PHP, check the error return codes that the DB will return. See https://www.php.net/manual/en/function.oci-error.php e.g.

    <?php
    $stid = oci_parse($conn, "your SQL statement goes here");
    $r = oci_execute($stid);
    if (!$r) {
        $e = oci_error($stid);  // For oci_execute errors pass the statement handle
        print htmlentities($e['message']);
        print "
    <pre>
    ";
        print htmlentities($e['sqltext']);
        printf("
    %".($e['offset']+1)."s", "^");
        print  "
    </pre>
    ";
    }
    ?>
    
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题