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