如何检查它是插入还是更新或删除在SQL Server触发器中如何检查它是更新还是删除? [英] 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..?

问题描述

我是SQL-SERVER触发器中的新手..

我想在此表上声明一个触发器

名称:ProductInfo
字段:
ProdID INT，
PName NVARCHAR(20)，
PCode NVARCHAR(20)，
PPrice NVARCHAR(10)

当有人在此ProductInfo表上触发任何查询(如INSERT，UPDATE，DELETE)时，该触发器将被触发，并且基于该操作(如INSERT或UPDATE或DELETE)，它将执行某些任务. />
我只想声明一个触发器来解决所有这些问题....

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....

推荐答案

--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

检查内容inserted 和deleted 表，如
所述 此处 [

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

选中此链接http://technet.microsoft.com/en-us/library/ms189799.aspx 参见主题:" B.将DML触发器与提醒电子邮件一起使用''

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''

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

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

这篇关于如何检查它是插入还是更新或删除在SQL Server触发器中如何检查它是更新还是删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！