If i am using a trigger which triggers on every update or Delete of my payment table, How can I identify the type of trigger which is occurred (If I explain more, the trigger function invoked because of updating a record or deleting a record.)
Because I need that information to store on my another table.
Instruction: when you insert data into table, only 'inserted' has the new inserted rows; when you delete data from table, only 'deleted' has the deleted rows; when you update table, the 'inserted' saves the new rows, the 'deleted' saves the old rows.
I think this sample can give you a hint. (SQL Server 2012)
Sample:
Sample Table Definition:
create table Customer (
ID int identity(1,1) not null,
FirstName varchar(30),
LastName varchar(30))
Trigger:
CREATE TRIGGER TrackAction
ON Customer
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @DELETEDCOUNT INT
DECLARE @INSERTEDCOUNT INT
SELECT @DELETEDCOUNT = COUNT() FROM deleted
SELECT @INSERTEDCOUNT = COUNT() FROM inserted
IF(@DELETEDCOUNT&@INSERTEDCOUNT > 0 )
PRINT 'Trigger by update action'
ELSE IF (@DELETEDCOUNT > 0 )
PRINT 'Trigger by delete action'
ELSE IF (@INSERTEDCOUNT > 0 )
PRINT 'Trigger by insert action'
END
Test code:
insert into Customer
values ('Bob','Ryan')
update customer
set FirstName = 'Bob Jr.'
where FirstName = 'Bob'
delete customer
where FirstName = 'Bob Jr.'
Test Result:If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With