Plsql trigger if updating

Posted by / 14-Jan-2021 00:41

Plsql trigger if updating

DML triggers cannot be defined on local or global temporary tables.DATABASE Applies the scope of a DDL trigger to the current database.In the image below we see where we create our example table named tbl Trigger Test with a primary key column named pk ID, a date column named Order Approval Date Time and a varchar column named Order Status.We populate the table with three test records with the Order Approval Date Time set to NULL and the Order Status set to "Pending".Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server.

Again, this is our desired behavior and all appears well.These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.Logon triggers fire in response to the LOGON event that is raised when a user's session is being established.select * from employee24; EMP_ID ---------- E_NAME ---------------------------------------------------------------------------- SAL ---------- 1001 mike 12000 1002 jake 13000 EMP_ID ---------- E_NAME ---------------------------------------------------------------------------- SAL ---------- 1003 made 14000 [email protected]; Trigger created Update employee24 set sal=sal 1000 where emp_id=1002; 1 ROW Updated -------- -------- -------- | emp_id | ename | salary | -------- -------- -------- | 1002 | jake | 14000 | -------- -------- -------- In the above example, the trigger is created for employee24 table, and in the employee24 table, update operation is performed on the salary of an employee by setting sal=sal 100 where emp_id=1002. Salary=15000; END IF; END; / Trigger created sql UPDATE Employee01 SET Salary=500; Query OK, 5 rows affected (0.04 sec) Rows matched: 7 Changed: 5 Warnings: 0 sql select * from employee01; -------- ---------- ----------- ------------------- -------- ------- | emp_id | emp_name | city | designation | salary | perks | -------- ---------- ----------- ------------------- -------- ------- | 1 | david | delhi | manager | 10000 | 855 | | 2 | shaha | mumbai | assistant manager | 10000 | 853 | | 3 | sha | puna | scales manager | 10000 | 850 | | 4 | jack | bangalore | designer | 10000 | 854 | | 5 | james | mangalore | web designer | 10000 | 1124 | | 6 | mike | chennai | develpoer | 10500 | 840 | -------- ---------- ----------- ------------------- -------- ------- 6 rows in set (0.00 sec)CREATE TRIGGER student_insert AFTER insert ON student_table FOR EACH ROW BEGIN INSERT into student_log1(user_id,description) VALUES (user(), CONCAT('INSER T student records',new.stu_id,' ',new.stu_name,' ',new.stu_class)); END$$ Query OK, 0 rows affected (0.15 sec) sql select * from student_log1; ---------------- --------------------------------- | user_id | description | ---------------- --------------------------------- | [email protected] | INSERT student records1 mike 10 | | [email protected] | INSERT student records2 mad 20 | | [email protected] | INSERT student records3 mack 30 | ---------------- --------------------------------- 3 rows in set (0.00 sec)In the above example, after insert trigger will fire the condition after inserting a record inside the table or on the table i.e, after creating student_insert table it will fire on the student_log1 table and all the values will be displayed in the student_log1 table which was inserted in the student_table.And, it has updated only one row based on the emp_id. CREATE TRIGGER stu_update AFTER UPDATE ON stu_table FOR EACH ROW BEGIN INSERT into stu_log(user_id, description) VALUES (user(), CONCAT('Update Student Record (',old.stu_id,' ',old.stu_name,' ',old.stu_class, ') to (',new.stu_id,' ',new.stu_name,' ',new.stu_class,')')); END; / sql select * from student_table02; -------- ---------- ----------- | stu_id | stu_name | stu_class | -------- ---------- ----------- | 1 | david | 9 | | 2 | shah | 9 | | 3 | mike | 9 | | 4 | james | 9 | -------- ---------- ----------- 4 rows in set (0.00 sec) sql select * from student_table02; -------- ---------- ----------- | stu_id | stu_name | stu_class | -------- ---------- ----------- | 1 | david | 10 | | 2 | shah | 10 | | 3 | mike | 10 | | 4 | james | 10 | -------- ---------- ----------- 4 rows in set (0.00 sec) sqlselect * from stu_log02; ---------------- -------------------------------------------------------- | user_id | description | ---------------- -------------------------------------------------------- | [email protected] | Update Student Record (1 david 9) to (1 david 10) | | [email protected] | Update Student Record (2 shah 9) to (2 shah 10) | | [email protected] | Update Student Record (3 mike 9) to (3 mike 10) | | [email protected] | Update Student Record (4 james 9) to (4 james 10) | ---------------- -------------------------------------------------------- In the above example, after update trigger will fire the condition after performing the update operation on a record inside the table or on the table.

plsql trigger if updating-5plsql trigger if updating-72plsql trigger if updating-44

DML events are INSERT, UPDATE, or DELETE statements on a table or view.

One thought on “plsql trigger if updating”

  1. The entitlement and amount of spousal support will depend on factors like: * your income and your spouse’s income, * your assets and your spouse’s assets, * your age and your spouse’s age, * your health and your spouse’s health, * the standard of living when you lived with your spouse, * your ability to become self-sufficient, * the contribution you made to your spouse’s career, and * the economic hardship suffered by you arising from the breakdown of the relationship.