Updating clause used in instead of trigger
*Action: Modify the underlying base tables directly.CREATE OR REPLACE TRIGGER trg_cust_proj_view_insert INSTEAD OF INSERT ON customer_projects_view DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customer_details (customer_id,customer_name,country) VALUES (:new.customer_id, :new.customer_name, :new.country); INSERT INTO projects_details (project_id, project_name, project_start_Date, customer_id) VALUES ( :new.project_id, :new.project_name, :new.project_start_Date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num= INSERT INTO customer_projects_view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',sysdate); INSERT INTO customer_projects_view VALUES (2,'ABC Infotech','India',202,'HR management',sysdate); CREATE OR REPLACE VIEW company_vehicles_view AS SELECT company.company_id, company.company_name, CAST (MULTISET (SELECT vehicle.vehicle_id, vehicle.vehicle_model_name FROM vehicle_details vehicle WHERE vehicle.company_id = company.company_id ) AS vehicle_details_list_ ) vehiclelist FROM vehicle_mfg_company_details company; -- no error.Sentry One Newsletters The bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.e News is a bi-monthly newsletter with fun information about Sentry One, tips to help improve your productivity, and much more.Summary: This article examines some surprising behaviour of INSTEAD OF triggers and reveals a serious cardinality estimation bug in SQL Server 2014.CREATE OR REPLACE TRIGGER trg_comp_vehicles_view_insrt INSTEAD OF INSERT ON NESTED TABLE vehiclelist OF company_vehicles_view REFERENCING NEW AS Vehicle PARENT AS Company FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table.INSERT INTO vehicle_details ( vehicle_id, company_id, vehicle_model_name ) VALUES ( : Vehicle.vehicle_id, : Company.company_id, : Vehicle.vehicle_model_name ); END; INSERT INTO TABLE ( SELECT vw.vehiclelist FROM company_vehicles_view vw WHERE company_id = 101 ) VALUES (1, 'Eco Sport'); INSERT INTO TABLE ( SELECT vw.vehiclelist FROM company_vehicles_view vw WHERE company_id = 101 ) VALUES (2, 'Endeavour'); Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion.
This is confusing, but it does at least disclose the presence of an INSTEAD OF trigger.
He is working in a well reputed MNC as an applications Developer with 5 years of experience.
He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development.
The underlying object is the same internal worktable we saw earlier, though here it is named table might not be what you were expecting (if you were expecting a seek on Row ID): This 'seek' returns all rows from the worktable that have an action code of 3 (delete), making it exactly equivalent to the Deleted Scan operator seen in AFTER trigger plans.
The same internal worktable is used to hold rows for both column alone.