i have following tables in mysql database:
item
column type null default links comments mime item_id (primary) int(11) no fk_item_category int(11) yes null item_category -> item_category_id item_name varchar(45) yes null item_description varchar(45) yes null item_price decimal(5,2) yes null item_category
column type null default links comments mime item_category_id (primary) int(11) no item_category_name varchar(45) yes null order
column type null default links comments mime order_id (primary) int(11) no fk_order_user_email varchar(45) yes null user -> user_email order_time datetime yes current_timestamp order_pickup_time time(6) yes null order_instructions varchar(45) yes null order_gross_total decimal(10,2) yes null order_net_total decimal(10,2) yes null order_detail
column type null default links comments mime order_details_id (primary) int(11) no fk_order_id int(11) yes null order -> order_id fk_order_item_id int(11) yes null item -> item_id order_detail_unit_price decimal(5,2) yes null order_detail_quantity int(11) yes null i have other tables too, trying create trigger on these tables specifically.
i trying create trigger add item_price order_detail table based on foreign key fk_order_item_id. trigger run after insert, , after update.
any appreciated. thanks
i recommend not using triggers in way. think application service correct location updating tables. if has in database consider use of stored procedure. should consider how going implement security of these tables. use of stored procedure can helpful, because can grant rights procedure user, not tables.
triggers can used conduct calculations before inserting or checking criteria.
i believe service in application best solution issue. should handle updates , security. in case might require multiple services. 1 creating order, possibly 1 updating after order placed. maybe deleting order, etc...
Comments
Post a Comment