Easy SQL Trigger...

TRIGGER

A TRIGGER IS A SPECIAL KIND OF STORED PROCEDURE THAT IS INVOKED WHENEVER AN ATTEMPT IS MADE TO MODIFY THE DATA IN THE TABLE IT PROTECTS. MODIFICATIONS TO THE TABLE ARE MADE USING INSERT, UPDATE, OR DELETE STATEMENTS. TRIGGERS ARE USED TO ENFORCE DATA INTEGRITY AND BUSINESS
RULES SUCH AS AUTOMATICALLY UPDATING SUMMARY DATA.

RULES

A TABLE CAN HAVE ONLY THREE TRIGGERS ACTION PER TABLE: UPDATE, INSERT, AND DELETE. ONLY TABLE OWNERS CAN CREATE AND DROP TRIGGERS FOR THE TABLE. THIS PERMISSION CANNOT BE TRANSFERRED. A TRIGGER CANNOT BE CREATED ON A VIEW OR A TEMPORARY TABLE BUT TRIGGERS CAN REFERENCE THEM. A TRIGGER
SHOULD NOT INCLUDE SELECT STATEMENTS THAT RETURN RESULTS TO THE USER, BECAUSE THE RETURNED RESULTS WOULD HAVE TO BE WRITTEN INTO EVERY APPLICATION IN WHICH MODIFICATIONS TO THE TRIGGER TABLE ARE
ALLOWED. THEY CAN BE USED TO HELP ENSURE THE RELATIONAL INTEGRITY OF DATABASE. ON DROPPING A TABLE ALL TRIGGERS ASSOCIATED TO THE TRIGGERS IS AUTOMATICALLY DROPPED.

NOW CREATE A TABLE AS WRITTEN BELOW. YOU DON'T NEED TO CREATE 'DEL_TABLE' TABLE BECAUSE IT WILL CREATED AUTOMATICALLY WHEN TRIGGER FIRED. IT WILL FIRED WHEN YOU DELETE RECORD FORM 'MAIN_TABLE' TABLE.
-----------------------------------------------------------------------------------------

CREATE TABLE [DBO].[MAIN_TABLE](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[S_NAME] [VARCHAR](50) NULL,
[S_MOB] [VARCHAR](50) NULL
)

------------------------------------------------------------------------------------------

CREATE TRIGGER TR_MAIN_TABLE_DELETE
ON MAIN_TABLE FOR DELETE AS

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[DEL_TABLE]') AND TYPE IN (N'U'))
    BEGIN
        CREATE TABLE [DBO].[DEL_TABLE](
        [IDD] [INT] IDENTITY(1,1) NOT NULL,
        [SS_NAME] [VARCHAR](50) NULL,
        [SS_MOB] [VARCHAR](50) NULL
        )
        INSERT INTO DEL_TABLE(SS_NAME,SS_MOB) (SELECT S_NAME, S_MOB FROM DELETED)
    END

ELSE
    BEGIN
        INSERT INTO DEL_TABLE(SS_NAME,SS_MOB) (SELECT S_NAME, S_MOB FROM DELETED)
    END

------------------------------------------------------------------------------------------------
--    TRUNCATE TABLE DEL_TABLE

--    TRUNCATE TABLE MAIN_TABLE

--    INSERT INTO MAIN_TABLE(S_NAME,S_MOB) VALUES('NITISH','XXXXXXXXXX')
--    INSERT INTO MAIN_TABLE(S_NAME,S_MOB) VALUES('KUMAR','XXXXXXXXXX')
--    INSERT INTO MAIN_TABLE(S_NAME,S_MOB) VALUES('JHA','XXXXXXXXXXXX')

--    DELETE FROM MAIN_TABLE WHERE ID=3

--    SELECT * FROM MAIN_TABLE

--    SELECT * FROM DEL_TABLE

--    DROP TABLE MAIN_TABLE

--    DISABLE TRIGGER TR_MAIN_TABLE_DELETE ON MAIN_TABLE

--    DROP TRIGGER DBO.TR_MAIN_TABLE_DELETE


GOOD LUCK!!!