View Forum Topic

Forums
CS422
Trigger Examples

Author	Message
cdecesa Posts: 8	Posted 15:32 Oct 23, 2010 \| Dr. Sun, Would it be possible for you to provide us some additional examples using triggers to enforce integrity constraints? So far, we have only seen fairly simple examples with triggers. I am having some difficulty figuring out how to create a trigger when multiple tables are involved, as is required for all of the problems in Homework 3. I would like to see an example for a more complicated trigger, such as the one listed on the sample midterm or the one we discussed in class about restricting the size of the Database class to 30 students. Thank you, Cristina
aligh1979 Posts: 121	Posted 23:08 Oct 23, 2010 \| I agree , we need more example .Other that could you please have one example of returning something else like a row instead of "null" . for a return value of a trigger function?
cysun Posts: 2935	Posted 16:16 Oct 24, 2010 \| I have updated plsql-create.sql and plsql-drop.sql to include a trigger that enforces the constraint that "the size of a database class cannot exceed 30".
cysun Posts: 2935	Posted 16:21 Oct 24, 2010 \| aligh1979 wrote: I agree , we need more example .Other that could you please have one example of returning something else like a row instead of "null" . for a return value of a trigger function? You can modify the db_class_size trigger as follows: 1. In the trigger definition, change "after insert or update" to "before insert or update". 2. In the db_class_size() procedure, Change the "raise exception" statement to "new.section_id = 54;" Change the "return null" statement to "return new;" Try insert some data into the enrollment table and see what happens. For testing purpose, you can reduce the class size limit from 30 to 3.
cdecesa Posts: 8	Posted 18:56 Oct 24, 2010 \| cysun wrote: I have updated plsql-create.sql and plsql-drop.sql to include a trigger that enforces the constraint that "the size of a database class cannot exceed 30". Thank you for including this example. I think this should clear up some questions I had about how to create more complicated triggers.
aligh1979 Posts: 121	Posted 21:29 Oct 24, 2010 \| I've been a bit experimenting with this new function trigger example that you have created , a lot of questions here. 1. for the first part which is after insert or update , first of all logic says if something should not be inserted or updated , so better to be "before " in the first place , why after? it seems the only thing that prevent this from being updated or inserted is the ""exception" not even returning "null" (since I changed the exception to "notice " and it went trough) . have not tried yet but it seems that if we had a return like "new" , the new would be inserted? 2.for the second part that was your suggestion and my question regarding return again. I did what you said . changing the raise exception to "new.section_id = 54;" and retuning "new" , but for some reason the section size went over the limit and it got inserted ! . I assume the expected thing was that whenever it is over the limit , assign the section id "54" and the return value insert or update the "new" of rowtype values? but it did not happen . 3. another thing that i am going to use it in my home works and I notice that on pgsql documentation page , is using if statement like this "if (TG_OP = 'UPDATE') then " just asking if it's Ok to use since you did not cover it in the class. thank you upfront.
cysun Posts: 2935	Posted 22:48 Oct 24, 2010 \| aligh1979 wrote: I've been a bit experimenting with this new function trigger example that you have created , a lot of questions here. 1. for the first part which is after insert or update , first of all logic says if something should not be inserted or updated , so better to be "before " in the first place , why after? it seems the only thing that prevent this from being updated or inserted is the ""exception" not even returning "null" (since I changed the exception to "notice " and it went trough) . have not tried yet but it seems that if we had a return like "new" , the new would be inserted? 2.for the second part that was your suggestion and my question regarding return again. I did what you said . changing the raise exception to "new.section_id = 54;" and retuning "new" , but for some reason the section size went over the limit and it got inserted ! . I assume the expected thing was that whenever it is over the limit , assign the section id "54" and the return value insert or update the "new" of rowtype values? but it did not happen . 3. another thing that i am going to use it in my home works and I notice that on pgsql documentation page , is using if statement like this "if (TG_OP = 'UPDATE') then " just asking if it's Ok to use since you did not cover it in the class. thank you upfront. 1. Only after a row is inserted/updated can you check if the class size is exceeded. "Raise exception" will rollback the changes if the size is exceeded. 2. My guess is that you forgot to change the trigger to a before-trigger. 3. Yes.
aligh1979 Posts: 121	Posted 00:02 Oct 25, 2010 \| thanks for your answer , regarding the second question , i had changed it to before . I am still working on the first part of homework and I am getting this error :"ERROR: syntax error at or near "begin" LINE 10: begin" and the whole code is : DELETED by cysun Last edited by cysun at 07:38 Oct 25, 2010.
cysun Posts: 2935	Posted 07:40 Oct 25, 2010 \| aligh1979 wrote: thanks for your answer , regarding the second question , i had changed it to before . I am still working on the first part of homework and I am getting this error :"ERROR: syntax error at or near "begin" LINE 10: begin" and the whole code is :... Posting complete code in the forum is not allowed. Do not do that again. Debugging is part of the work. You need to figure it out yourself.