locked
How to delete the record using trigger if inserted record is match? and then insert that record into that table.? RRS feed

  • Question

  • Hi frnz,

    i have a scenario i.e i have to create a trigger that performs the operations on table like..

    i have Checkdate column in my table ,so i want to insert any new  Checkdate record into that table using that Checkdate column ,here if record is match .i.e Checkdate is match then delete that record and insert that new checkdate  record into that table.here am performg the operation on single table only..

    for this hw should we create the trigger and perform this operation..

    can you please create the trigger perform this operation...am new to create the trigger here..

    can you pls let me know....

    Thanks!


    Wednesday, October 15, 2014 6:33 AM

Answers

  • Please try like the following code:

    DROP TABLE dbo.JustForTest
    GO
    
    CREATE TABLE JustForTest 
    ( CheckDate DATE , Name NVARCHAR(128) ) ;
    
    GO
    
    CREATE TRIGGER trgTest ON dbo.JustForTest
    INSTEAD OF INSERT
    AS 
    DELETE t
    FROM dbo.JustForTest AS t 
    JOIN Inserted AS i ON i.CheckDate = t.CheckDate ;
    
    INSERT dbo.JustForTest
            ( CheckDate, Name )
    SELECT CheckDate, Name
    FROM Inserted ;
    GO
    
    --test 
    INSERT dbo.JustForTest
            ( CheckDate, Name )
    VALUES  ( '10/15/2014', 'first' ), ( '10/16/2014', 'second' ); 
    
    INSERT dbo.JustForTest
            ( CheckDate, Name )
    VALUES  ( '10/15/2014', 'third' ); 
    
    SELECT	* 
    FROM	dbo.JustForTest


    T-SQL Articles
    T-SQL e-book by TechNet Wiki Community
    T-SQL blog

    • Marked as answer by Ychinnari Wednesday, October 15, 2014 10:38 AM
    Wednesday, October 15, 2014 6:55 AM
  • By using Instead of Trigger we can get solution for your requirement. Whenever the Insert operation takes place in table dbo.Table1, this trigger will fire. But it will not insert data into a table, instead of that it will check the new data with existing data and comapare, if both are matching it will delete the entire row and insert the new rocord.

    CREATE TRIGGER trgInsertDelete ON dbo.Table1
    INSTEAD OF INSERT
    AS 
    BEGIN
    DELETE TAB1 FROM dbo.Table1 AS TAB1 
    JOIN Inserted AS I ON I.CheckDate = T1.CheckDate ;
    
    INSERT dbo.Table1
            ([ServerName],
            [BackupType],
            [BackupStatus],
            [CheckDate],
            [CheckTime])
            
    SELECT ([ServerName],
            [BackupType],
            [BackupStatus],
            [CheckDate],
            [CheckTime])
    FROM Inserted ;
    
    END

    Venkat G


    • Edited by Venkat G Wednesday, October 15, 2014 7:39 AM
    • Marked as answer by Ychinnari Wednesday, October 15, 2014 10:39 AM
    Wednesday, October 15, 2014 7:38 AM
  • You may need to add Servername check as well to your join condition as below. Again, As Uri Suggested you may not need a TRIGGER here...

    DELETE TAB1 FROM dbo.Table1 AS TAB1 
    JOIN Inserted AS I ON I.CheckDate = TAB1.CheckDate and  TAB1.ServerNAme = I.ServerName;


    • Marked as answer by Ychinnari Wednesday, October 15, 2014 10:39 AM
    Wednesday, October 15, 2014 9:01 AM
    Answerer

All replies

  • 1. If  new entry of checkdate value is matching with your existing checkdate, you need to delete the existing record.

    2. Do you want to store the deleted records in another table?

    Basically triggers are used for maintaining the audit information of INSERT,DELETE,UPDATE operations will takes place in one table. We can maintain the historical data in another table using triggers.

    I suggest you to explain the requirement clearly..


    Venkat G

    Wednesday, October 15, 2014 6:49 AM
  • Please try like the following code:

    DROP TABLE dbo.JustForTest
    GO
    
    CREATE TABLE JustForTest 
    ( CheckDate DATE , Name NVARCHAR(128) ) ;
    
    GO
    
    CREATE TRIGGER trgTest ON dbo.JustForTest
    INSTEAD OF INSERT
    AS 
    DELETE t
    FROM dbo.JustForTest AS t 
    JOIN Inserted AS i ON i.CheckDate = t.CheckDate ;
    
    INSERT dbo.JustForTest
            ( CheckDate, Name )
    SELECT CheckDate, Name
    FROM Inserted ;
    GO
    
    --test 
    INSERT dbo.JustForTest
            ( CheckDate, Name )
    VALUES  ( '10/15/2014', 'first' ), ( '10/16/2014', 'second' ); 
    
    INSERT dbo.JustForTest
            ( CheckDate, Name )
    VALUES  ( '10/15/2014', 'third' ); 
    
    SELECT	* 
    FROM	dbo.JustForTest


    T-SQL Articles
    T-SQL e-book by TechNet Wiki Community
    T-SQL blog

    • Marked as answer by Ychinnari Wednesday, October 15, 2014 10:38 AM
    Wednesday, October 15, 2014 6:55 AM
  • I do not think you need a trigger here

    IF EXISTS (SELECT * FROM tbl WHERE checkdate=@checkdate)

         DELETE FROM tbl WHERE checkdate=@checkdate

         INSERT INTO tbl (columns go here) VALUES (........)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 15, 2014 6:55 AM
  • In your scenario trigger is not needed.

    You can use merge statements. check for checkdate with supplied value of checkdate.

    If it is matching than update this record else insert new record.

    Why you want to delete that record and insert new record? Why don't you go for update that record.


    dharampal sikhwal

    Wednesday, October 15, 2014 7:07 AM
  • By using Instead of Trigger we can get solution for your requirement. Whenever the Insert operation takes place in table dbo.Table1, this trigger will fire. But it will not insert data into a table, instead of that it will check the new data with existing data and comapare, if both are matching it will delete the entire row and insert the new rocord.

    CREATE TRIGGER trgInsertDelete ON dbo.Table1
    INSTEAD OF INSERT
    AS 
    BEGIN
    DELETE TAB1 FROM dbo.Table1 AS TAB1 
    JOIN Inserted AS I ON I.CheckDate = T1.CheckDate ;
    
    INSERT dbo.Table1
            ([ServerName],
            [BackupType],
            [BackupStatus],
            [CheckDate],
            [CheckTime])
            
    SELECT ([ServerName],
            [BackupType],
            [BackupStatus],
            [CheckDate],
            [CheckTime])
    FROM Inserted ;
    
    END

    Venkat G


    • Edited by Venkat G Wednesday, October 15, 2014 7:39 AM
    • Marked as answer by Ychinnari Wednesday, October 15, 2014 10:39 AM
    Wednesday, October 15, 2014 7:38 AM
  • Hi frnz,

    here its work fine but some problem with the query..

    i.e when i insert the Checkdate record into that table it will check that record in the table and delete and insert the record but,

    when i insert the same checkdate with the different  Servername   it will not taken it just update the same record with the same CheckDate.here i need both of the records

    1)first i insert the record with the CheckDate(2014-10-13) with the serverA it will insert the below

    insert into  dbo.Table1 values( 'ServerA','DB ','Failed','2014-10-13','09.25.00')

    o/p:

    ServerA          DB         Failed     2014-10-13 00:00:00.0000000

    2)second i  will insert the record with the Servername : serverB with the same Checkdate(2014-10-13) it will replace with ServerA with the same Servername gives ServerB.

    insert into  dbo.Table1 values( 'ServerB','DB ','Failed','2014-10-13','09.25.00')

    o/p:

    ServerB          DB         Failed     2014-10-13 00:00:00.0000000(i.e mence it will replace the servername.But here i need the both records like below)

    ServerA          DB         Failed     2014-10-13 00:00:00.0000000ServerB          DB         Failed     2014-10-13 00:00:00.0000000

    but here i need both the records ServerA, serverB..

    Can u see and modify the Trigger Query

    pls see the below o/p :

    Servername     path           Status       CheckDate        CheckTime

    ServerA          DB         Failed     2014-10-13 00:00:00.0000000
    WLEUCM01  DB         Failed     2014-08-01 08:40:32.0000000
    WDEUSQL01  DB         Failed     2014-08-01 08:40:53.0000000
    WLEM01          DB         Failed     2014-10-07 07:03:10.0000000
    WDEMSQL01  DB         Failed     2014-10-07 07:03:47.0000000

    Wednesday, October 15, 2014 8:51 AM
  • You may need to add Servername check as well to your join condition as below. Again, As Uri Suggested you may not need a TRIGGER here...

    DELETE TAB1 FROM dbo.Table1 AS TAB1 
    JOIN Inserted AS I ON I.CheckDate = TAB1.CheckDate and  TAB1.ServerNAme = I.ServerName;


    • Marked as answer by Ychinnari Wednesday, October 15, 2014 10:39 AM
    Wednesday, October 15, 2014 9:01 AM
    Answerer
  • Here my requirement is create a trigger pls create a trigger and help me out for this.

    CREATE TABLE #BackupStatus(
    [ServerName] [varchar](50) NULL,
    [BackupType] [char](10) NULL,
    [BackupStatus] [char](10) NULL,
    [CheckDate] [date] NULL,
    [CheckTime] [time](7) NULL
    )

    this is my table structure...

    Why?

    Who told you need a trigger?

    Is this a class assignment?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, October 15, 2014 10:00 AM
  • I think what you need is just this

    INSERT dbo.Table1
    (ServerName,BackupType,BackupStatus,CheckDate,CheckTime)
    SELECT *
    FROM
    (
    MERGE dbo.Table1 AS d
    USING (VALUES(@ServerName,@BackupType,@BackupStatus,@CheckDate,@CheckTime))AS s (ServerName,BackupType,BackupStatus,CheckDate,CheckTime)
    ON s.ServerName = d.ServerName
    AND s.CheckDate = d.CheckDate
    WHEN MATCHED
    THEN DELETE
    WHEN NOT MATCHED
    THEN INSERT (ServerName,BackupType,BackupStatus,CheckDate,CheckTime)
    VALUES (s.ServerName,s.BackupType,s.BackupStatus,s.CheckDate,s.CheckTime)
    OUTPUT s.ServerName,s.BackupType,s.BackupStatus,s.CheckDate,s.CheckTime,$action
    ) AS t (ServerName,BackupType,BackupStatus,CheckDate,CheckTime,Action)
    WHERE Action = 'DELETE'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, October 15, 2014 10:10 AM