how to check whether it is inserte or update or delete in how to check whether it is update or delete in sql server trigger..?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I am new in Trigger in SQL-SERVER..

I want to declare a Trigger on this table

Name : ProductInfo
Fields :
ProdID INT,
PName NVARCHAR(20),
PCode NVARCHAR(20),
PPrice NVARCHAR(10)

what i want to do when some one fire any query like INSERT, UPDATE ,DELETE on this ProductInfo Table at that time that trigger will be fired and based on the operation like if INSERT or UPDATE or DELETE it will perform some task..

I want to declare only one trigger that will take care for all this....

Posted 11-Sep-11 23:01pm

Tejas Vaishnav

Add a Solution

4 solutions

Solution 1

Check contents of inserted and deleted tables as described
here[^]

Posted 11-Sep-11 23:22pm

Timberbird

Comments

Tejas Vaishnav 12-Sep-11 5:31am

Thanks for your reply, But I already done...

Solution 3

Check this link http://technet.microsoft.com/en-us/library/ms189799.aspx see the topic : 'B. Using a DML trigger with a reminder e-mail message'

SQL

CREATE TRIGGER TrgProductInfo
ON ProductInfo
AFTER INSERT, UPDATE, DELETE 
AS
   Code for Insert,Update,delete
GO

For updates, the original values for the row will be added to the deleted table, and the new values for the row will be added to the inserted table. So, to identify inserts, deletes and updates you would do the following

SQL

Insert - get the rows from inserted that are not in deleted
Delete - get the rows from deleted that are not in inserted.
Update - get the rows that are in both inserted and deleted

Posted 11-Sep-11 23:34pm

AditSheth

Comments

Tejas Vaishnav 12-Sep-11 5:50am

Thanks

Solution 2

create trigger your_trigername on ProductInfo
for insert, delete, update
as
if ((select count(*) from Deleted) > 1)
Begin
--Your task for delete command goes here
rollback transaction
End

If ((select count(*) from Inserted) > 1)
Begin
--Your task for delete command goes here
--If you want you can roll back the transcation
rollback transaction
End

If ((select count(*) from Updated) > 1)
Begin
--Your task for delete command goes here
rollback transaction
End

Posted 11-Sep-11 23:31pm

Thiagarajan Duraisamy

Comments

Tejas Vaishnav 12-Sep-11 5:49am

Thanks

AditSheth 12-Sep-11 7:09am

Hello...
select count(*) from Updated
Table named "Updated" is not exists
When it is updated "get the rows that are in both inserted and deleted"

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Accepted Answer · 2011-09-11T23:43:00

SQL

--first of create some testing tables in database
-- create table code starts here
--DROP TABLE dbo.TestTable
CREATE TABLE TestTable (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(20),
	Pass NVARCHAR(20)
)

--DROP TABLE dbo.TR_Table
CREATE TABLE TR_Table (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	DelID INT,
	DelName NVARCHAR(20),
	DelPass NVARCHAR(20),
	Opration NVARCHAR(20),
	OpDate DATETIME
)

-- create table code ends here

--Tigger Code starts Here
-- this code is used in saprate sql query otherwise it will give error 
-- means not use with create table, or if you want to use please select only trigger query then execute it 


CREATE TRIGGER MY_TEST_TRIGGER
   ON  TestTable
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	DECLARE @DelID INT;
	DECLARE @DelName NVARCHAR(20);
	DECLARE @DelPass NVARCHAR(20);
	DECLARE @Operation NVARCHAR(20);
	DECLARE @ACTION CHAR(1);
    DECLARE @COUNT INT;
	SET @ACTION = 'I';
	
	SELECT @COUNT = COUNT(*) FROM DELETED
	
	IF (@COUNT > 0)
	BEGIN
		SET @Action = 'D';
		SELECT @COUNT = COUNT(*) FROM INSERTED
		IF (@COUNT > 0)
		BEGIN
			SET @Action = 'U';
		END
	END
	
	IF (@Action = 'D')
		BEGIN
			SET @Operation = 'DELETION'
			SELECT @DelID = d.ID, @DelName = d.Name, @DelPass = d.pass FROM DELETED d
			INSERT INTO TR_Table (DelID,DelName,DelPass,Opration,OpDate) 
			VALUES (@DelID,@DelName,@DelPass,@Operation,GETDATE())
		END
	ELSE IF (@Action = 'I')
		BEGIN
			SET @Operation = 'INSERTION'
			SELECT @DelID = i.ID, @DelName = i.Name, @DelPass = i.pass FROM INSERTED i
			INSERT INTO TR_Table (DelID,DelName,DelPass,Opration,OpDate) 
			VALUES (@DelID,@DelName,@DelPass,@Operation,GETDATE())
		END
	ELSE
		BEGIN
			SET @Operation = 'UPDATION'
			SELECT @DelID = i.ID, @DelName = i.Name, @DelPass = i.pass FROM INSERTED i
			INSERT INTO TR_Table (DelID,DelName,DelPass,Opration,OpDate) 
			VALUES (@DelID,@DelName,@DelPass,@Operation,GETDATE())
		END
END
GO

-- Trigger code ends here


--this is some testing query for insert update delete

INSERT INTO dbo.TestTable (NAME,PASS) VALUES ('Tejas','Tejas123')

UPDATE dbo.TestTable SET NAME = 'Tejas Vaishnav', PASS = 'Admin123' WHERE NAME = 'Tejas'

DELETE FROM dbo.TestTable WHERE NAME = 'Tejas Vaishnav'


-- to see trigger out put use this query

SELECT * FROM TR_Table