Trigger updating another table

Rated 4.95/5 based on 625 customer reviews

CREATE OR REPLACE TRIGGER trg_log_employee_salary AFTER UPDATE OF SALARY ON employee_salary FOR EACH ROW DECLARE username varchar2(20); BEGIN -- get current login user SELECT USER INTO username FROM dual; -- Insert new values into log table. INSERT INTO employee_salary_hike_log VALUES ( : NEW. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. SALARY, sysdate, username); END; INSERT INTO employee_salary VALUES (101,15000,'Pranav'); INSERT INTO employee_salary VALUES (201,40000,'Vikram'); INSERT INTO employee_salary VALUES (301,35000,'Nikhil'); -- fire trigger, insert into log table UPDATE employee_salary SET SALARY = '28000' WHERE emp_id = 101; -- fire trigger, insert into log table UPDATE employee_salary SET SALARY = '43000' WHERE emp_id = 301; 50000) DECLARE username varchar2(20); BEGIN SELECT USER INTO username FROM dual; -- Insert new values into log table. He is working in a well reputed MNC as an applications Developer with 5 years of experience.They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update?And do they contain a single row per row of data changed or are they truly tables? These two tables if they contain anything, contain a row for every row of data affected and therefore should be treated as tables.I had a situation recently where we were changing an internal process that meant we would no longer be updating the data in a specific table because those column where moving to another table.Unfortunately there was going to be some crossover and a temporary trigger may be necessary to continue to populate the previous table.Too many times we see cursors going row-by-row through these tables when a set based operation would have been better.

And since they should be treated as tables, use set-based operations on them whenever you can.

The new data types to use are VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) respectively.

Any new database design should make use of these new data types.

While this isn't what actually happens, it helps visualize what the tables contain and is useful in that sense.

Let's see an example of the triggers in operation with these tables. Now there is a catch you should be aware of with AFTER triggers (the default type of trigger unless you manually specify that's an INSTEAD OF trigger).

Leave a Reply