delete with Y flag and I with N

Question

please note this is no duplicate question,here output is different HI EXPERT,

create tabletest19 (col1 char(23), col2 CHAR(20),col3 char(3),col4 CHAR(23),col5 CHAR(23),col6 DATETIME )and expected output is this

insert into tabletest19 VALUES('I','2','3','4','N','2024-02-14 16:34:39.483') 
insert into tabletest19 VALUES('D','2','3','4','Y','2024-02-14 16:34:40.483') 
insert into tabletest19 VALUES('D','4','5','6','Y','2024-02-14 16:34:39.483') 
insert into tabletest19 VALUES('I','2','3','4','N','2024-02-14 16:34:40.483')

CASE WHEN NEW RECORD INSERTED(I) AND 2ND STATEMNETS COMES AS DELETE (D) THE SAME RECORD THEN OUTPUT SQLCopy

('D','2','3','4','Y','2024-02-14 16:34:39.483') 

CASE WHEN NEW RECORD DELETED(D) AND 2ND STATEMNETS COMES AS INSERTED(I) THE SAME RECORD THEN OUTPUT SQLCopy

('I','2','3','4','N','2024-02-14 16:34:40.483') 

Answer 1

You need a logic to either keep or remove records based on the sequence of insert ('I') and delete ('D') operations. This is how you can proceed :

• Creating a trigger or a stored procedure that checks the existing records before inserting a new one.
• If an insert operation ('I') is followed by a delete operation ('D') for the same record (based on matching key fields), you would either not insert the delete operation or mark it as deleted based on your application logic.
• Similarly, if a delete operation ('D') is followed by an insert operation ('I'), you would insert the new record or update the existing record's status.

CREATE TRIGGER trgAfterInsertOrUpdate
ON tabletest19
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Example: Delete a previously inserted record if a new delete ('D') operation is inserted for the same record.
    -- This requires a unique identifier or a combination of columns to identify the "same" record.
    DELETE FROM tabletest19
    WHERE col1 = 'D' AND EXISTS (
        SELECT 1 FROM inserted i
        WHERE tabletest19.col2 = i.col2
        AND tabletest19.col3 = i.col3
        AND tabletest19.col4 = i.col4
        AND tabletest19.col5 = 'Y'
        AND i.col5 = 'N'
    );
END

Answer 2

all your questions have the fallacy that sql tables (sets) have an insert order. this is not true. the order will change if you perform deletes, updates, or the table just grows large (due to bucket splits).

without an order by clause, the order of the set is undefined. none of your sample include an order by clause that defines the sets order. without an order clause, there is no first, second or last row.