Inserting updating deleting trigger events bigbeautifulwomendating org
CREATE TRIGGER justatest ON testtable FOR UPDATE, INSERT, DELETEASBEGIN IF @@update-fired-trigger = 1 INSERT INTO log 'a record was updated' ELSE IF @@insert-fired-trigger = 1 INSERT INTO log 'a record was inserted' ELSE IF @@delete-fired-trigger = 1 INSERT INTO log 'a record was deleted'ENDBasically, if there are rows in both inserted and deleted, it's an update.If there are rows only in inserted, it's an insert.When the trigger is associated with the entire database, the event can either be CREATE TABLE, DROP TABLE, CREATE INDEX and DROP INDEX.In the first example, assume that you have a table named CREATE OR REPLACE TRIGGER insert_set_salary NO CASCADE BEFORE INSERT ON employee REFERENCING NEW AS N FOR EACH ROW -- Give starting salary based on education level.Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .The new predicates, INSERTING, UPDATING, and DELETING, are self-explanatory and refer to the event that caused the trigger to be fired.
In the above program, inserting and updating are the key words which are used to identify the events.
Triggers can be classified into two types, basing on the no of times it is executed.
1) Statement level triggers 2) Row level triggers 1) Statement level triggers are executed only once, irrespective of no of rows effected by the event.
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.