为什么我使用上面这段代码后student表中的tot_cred变成了NULL??

可能是因为在执行触发器之前，student 表中的 tot_cred 字段已经被设置为 NULL。如果这是情况，那么在执行 UPDATE 语句时，tot_cred 字段的值将仍然是 NULL。为了解决这个问题，可以在 UPDATE 语句中使用 COALESCE 函数，将 NULL 值替换为 0。修改后的触发器代码如下所示：

create trigger tr_insert on takes for insert as 
declare @sno varchar(5) 
declare @cno varchar(5) 
declare @credit numeric(3,0) 

select @sno=ID,@cno=course_id from inserted  
select @credit=credits  from course  where course_id=@cno 

update student set tot_cred=COALESCE(tot_cred, 0) + @credit  
where ID=@sno 

go

这样，即使 tot_cred 字段的值为 NULL，触发器也会将其视为 0，并将 @credit 值加到 tot_cred 中。

相关问题

给出100条sql插入语句，插入表student，有ID，name，dept_name, tot_cred四个列，ID为五位int类型，tot_cred 为学分大于零小于150

以下是100条SQL插入语句，插入表student，有ID，name，dept_name, tot_cred四个列，其中ID为五位int类型，tot_cred 为学分大于零小于150：

INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10001, 'John Smith', 'Comp. Sci.', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10002, 'Jane Doe', 'Comp. Sci.', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10003, 'Bob Johnson', 'Comp. Sci.', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10004, 'Mary Lee', 'Comp. Sci.', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10005, 'Tom Brown', 'Comp. Sci.', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10006, 'Sue Davis', 'Comp. Sci.', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10007, 'Jim Wilson', 'Comp. Sci.', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10008, 'Carol Smith', 'Comp. Sci.', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10009, 'David Miller', 'Comp. Sci.', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10010, 'Linda Johnson', 'Comp. Sci.', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10011, 'Mike Brown', 'Comp. Sci.', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10012, 'Amy Lee', 'Comp. Sci.', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10013, 'Jack Davis', 'Comp. Sci.', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10014, 'Jill Wilson', 'Comp. Sci.', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10015, 'Sam Smith', 'Comp. Sci.', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10016, 'Janet Johnson', 'Comp. Sci.', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10017, 'Bob Brown', 'Math', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10018, 'Mary Davis', 'Math', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10019, 'Tom Wilson', 'Math', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10020, 'Sue Smith', 'Math', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10021, 'Jim Lee', 'Math', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10022, 'Carol Johnson', 'Math', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10023, 'David Brown', 'Math', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10024, 'Linda Davis', 'Math', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10025, 'Mike Wilson', 'Math', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10026, 'Amy Smith', 'Math', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10027, 'Jack Lee', 'Math', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10028, 'Jill Johnson', 'Math', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10029, 'Sam Brown', 'Math', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10030, 'Janet Davis', 'Math', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10031, 'Bob Wilson', 'Physics', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10032, 'Mary Smith', 'Physics', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10033, 'Tom Lee', 'Physics', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10034, 'Sue Johnson', 'Physics', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10035, 'Jim Brown', 'Physics', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10036, 'Carol Davis', 'Physics', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10037, 'David Wilson', 'Physics', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10038, 'Linda Smith', 'Physics', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10039, 'Mike Lee', 'Physics', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10040, 'Amy Johnson', 'Physics', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10041, 'Jack Brown', 'Physics', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10042, 'Jill Davis', 'Physics', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10043, 'Sam Wilson', 'Physics', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10044, 'Janet Smith', 'Physics', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10045, 'Bob Lee', 'Biology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10046, 'Mary Johnson', 'Biology', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10047, 'Tom Brown', 'Biology', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10048, 'Sue Davis', 'Biology', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10049, 'Jim Wilson', 'Biology', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10050, 'Carol Smith', 'Biology', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10051, 'David Miller', 'Biology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10052, 'Linda Johnson', 'Biology', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10053, 'Mike Brown', 'Biology', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10054, 'Amy Lee', 'Biology', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10055, 'Jack Davis', 'Biology', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10056, 'Jill Wilson', 'Biology', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10057, 'Sam Smith', 'Biology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10058, 'Janet Johnson', 'Biology', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10059, 'Bob Brown', 'Chemistry', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10060, 'Mary Davis', 'Chemistry', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10061, 'Tom Wilson', 'Chemistry', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10062, 'Sue Smith', 'Chemistry', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10063, 'Jim Lee', 'Chemistry', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10064, 'Carol Johnson', 'Chemistry', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10065, 'David Brown', 'Chemistry', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10066, 'Linda Davis', 'Chemistry', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10067, 'Mike Wilson', 'Chemistry', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10068, 'Amy Smith', 'Chemistry', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10069, 'Jack Lee', 'Chemistry', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10070, 'Jill Johnson', 'Chemistry', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10071, 'Sam Brown', 'Geology', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10072, 'Janet Davis', 'Geology', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10073, 'Bob Wilson', 'Geology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10074, 'Mary Smith', 'Geology', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10075, 'Tom Lee', 'Geology', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10076, 'Sue Johnson', 'Geology', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10077, 'Jim Brown', 'Geology', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10078, 'Carol Davis', 'Geology', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10079, 'David Wilson', 'Geology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10080, 'Linda Smith', 'Geology', 150);

注意：这些语句仅是示例，仅可用于学习和测试目的，不应用于实际生产环境。

len和tot_len有什么区别？我分不清.最好列出代码讲解

`len`和`tot_len`两个字段的区别在于，`len`字段表示的是该pbuf中实际包含的数据的长度，而`tot_len`字段则表示整个pbuf链表中所有pbuf所包含的数据的总长度。下面是一个简单的示例代码，用于演示它们的区别：

#include "lwip/pbuf.h"
#include "stdio.h"

int main() {
    // 创建一个pbuf链表，包含两个pbuf
    struct pbuf* p1 = pbuf_alloc(PBUF_RAW, 10, PBUF_POOL);
    struct pbuf* p2 = pbuf_alloc(PBUF_RAW, 20, PBUF_POOL);
    pbuf_chain(p1, p2);

    // 输出每个pbuf的len和tot_len字段
    printf("p1->len = %d, p1->tot_len = %d\n", p1->len, p1->tot_len);
    printf("p2->len = %d, p2->tot_len = %d\n", p2->len, p2->tot_len);

    // 释放pbuf链表
    pbuf_free(p1);
    return 0;
}

在这个示例代码中，我们创建了一个包含两个pbuf的链表，其中第一个pbuf的`len`字段为10，`tot_len`字段为30（因为它包含了整个链表的所有数据），而第二个pbuf的`len`字段为20，`tot_len`字段为20（因为它是链表中的最后一个pbuf）。这个示例代码只是一个简单的演示，实际上在实际的网络通信中，pbuf链表可能包含更多的pbuf，以表示更大的数据包。