How to use if updating on nested column in triggers
CREATE OR REPLACE VIEW customer_projects_view AS SELECT cust.customer_id, cust.customer_name, cust.country, projectdtls.project_id, projectdtls.project_name, projectdtls.project_start_Date FROM customer_details cust, projects_details projectdtls WHERE cust.customer_id = projectdtls.customer_id; INSERT INTO customer_projects_view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',sysdate); -- output SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779.
PDF (US Ltr) - 26.9Mb PDF (A4) - 26.9Mb PDF (RPM) - 25.3Mb HTML Download (TGZ) - 6.3Mb HTML Download (Zip) - 6.3Mb HTML Download (RPM) - 5.3Mb Man Pages (TGZ) - 158.7Kb Man Pages (Zip) - 262.0Kb Info (Gzip) - 2.6Mb Info (Zip) - 2.6Mb My SQL Backup and Recovery My SQL NDB Cluster 7.2 My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows If you drop a table, any triggers for the table are also dropped.
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema.
create view employee_v as select id, name, my_decrypt (salary) from employee_t CREATE TRIGGER Employee Insert ON employee_v INSTEAD OF INSERT AS BEGIN INSERT employee_t SELECT id, name, my_encrypt (salary) FROM inserted END CREATE TRIGGER employee Update ON employee_v INSTEAD OF UPDATE AS BEGIN DELETE FROM employee_t WHERE id IN (SELECT id FROM deleted) INSERT employee_t SELECT id, name, my_encrypt (salary) FROM inserted END CREATE TRIGGER employee Delete ON employee_v INSTEAD OF DELETE AS BEGIN DELETE FROM employee_t WHERE id IN (SELECT id FROM deleted) END You can use the set command inside a trigger.
The set option you invoke remains in effect during the execution of the trigger, then reverts to its former setting.
if update allows specified trigger actions to be associated with updates to specified columns (see Example 3).
More than one column can be specified, and you can use more than one if update statement in a create trigger statement (see Example 5).INSERT INTO vehicle_mfg_company_details VALUES (101,'Ford'); -- error INSERT INTO TABLE ( SELECT vw.vehiclelist FROM company_vehicles_view vw WHERE company_id = 101 ) VALUES (1, 'Eco Sport'); -- output SQL Error: ORA-25015: cannot perform DML on this nested table view column 25015.00000 - "cannot perform DML on this nested table view column" *Cause: DML cannot be performed on a nested table view column except through an INSTEAD OF trigger *Action: Create an INSTEAD OF trigger over the nested table view column and then perform the DML.*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.Permissions on objects at trigger creation – When you create a trigger, Adaptive Server makes no permission checks on objects such as tables or views that the trigger references.