Delete Records Within Instead of Delete Trigger

Delete records within Instead Of Delete trigger

This method should do the trick. An instead of trigger will do whatever you specify instead of doing the delete automatically. This means it is key that you do the delete in it manually or the record will not get deleted. It will not run recursively. It can only be done on a table without cascade delete enabled. Basically the trick is you join to the orginal table on the id field in order to get the data from the field you don't have access to in the deleted pseudotable.

create table dbo.mytesting (test_id int, sometext text)
go
create table dbo.myaudit (test_id int, sometext text)
go
insert into dbo.mytesting
values (1, 'test')
go

Create Trigger audit_Table_Deletes on dbo.mytesting INSTEAD OF delete  
as 
if @@rowcount = 0 return; 
Insert into dbo.myaudit (test_id, sometext) 
Select d.test_id, t.sometext from deleted d 
join dbo.mytesting t on t.test_id = d.test_id

Delete dbo.mytesting where test_id in (select test_id from deleted)
go

delete dbo.mytesting where test_id = 1
select * from dbo.mytesting
select * from dbo.myaudit 
Go 

drop table dbo.mytesting
drop table dbo.myaudit

If you can change the field to varchar(max) or nvarchar(max) that is the best solution though. Text and ntext are deprecated and should be removed altogether from SQL Server in the next version.

Trigger to delete the record from the table if it exists in it

A simple DELETE + JOIN should detect whether the record exists in the future and can then be deleted.

The following delete can be the entire body of your trigger.

    DELETE F
    FROM employee_assignment_future F
    INNER JOIN Inserted I
        ON I.employee_id = F.employee_id 
        AND I.department_id = F.department_id
        AND I.job_title_id = F.job_title_id
        AND I.manager_id = F.manager_id;

Note: You can't assume Inserted (or Deleted) have only a single row. You need to use set based operations and handle it having 0-N rows.

How to use instead of delete trigger and still delete the row in SQL Server

The problem has been resolved with help of @ZLK using cascading deletes or:

@ZLK: You also have the option of altering the table to not check the constraint. For example, alter table X nocheck constraint myConstraintName, then doing the delete statement / data manipulation (e.g. in an after delete trigger) then turning the constraint back on alter table X check constraint myConstraintName

Replace After Delete trigger by Instead of trigger

An INSTEAD OF trigger can decide what to do instead of facing changed records as with an AFTER trigger. This is mostly used for views that are based on multiple tables and that need to be updateable. The trigger then has to direct the updates to the correct underlying tables.

In your case, you would do

CREATE TRIGGER T1 ON Employee INSTEAD OF DELETE
AS
  DELETE FROM Employee
  FROM Employee e
    INNER JOIN deleted d ON e.EmployeeID = d.EmployeeID
  WHERE e.salary <= 5000

Make sure that recursive triggers are disabled.

how to get deleting rows count in before delete trigger in oracle

A delete statement can affect many rows. There are four events on which you can place a trigger:

before statement where all you know is that 0 to n deletes are going to take place
before each row where you only see one row that is about to get deleted
after each row where you only see that one row again
after statement where all you know is that that 0 to n deletes have taken place

So none of these trigger types alone helps you with your task. What you want is a compound trigger, where you can count the affected rows in the before or after each row section and look at the total in the after statement section.

CREATE OR REPLACE TRIGGER trg_check_max_deletes
FOR DELETE ON mytable COMPOUND TRIGGER
  v_count INTEGER := 0;

  AFTER EACH ROW IS
  BEGIN
    v_count := v_count + 1;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    IF v_count > 3 THEN
      RAISE_APPLICATION_ERROR(-20000, 'You are not allowed to delete more than 3 rows at once.');
    END IF;
  END AFTER STATEMENT;
END trg_check_max_deletes;

Demo: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=ba801c67c9e63eadaff1f233debacc00

Instead of delete trigger triggers two times

This is because there were 2 sets of data effect, the set outside the TRIGGER, and then again inside it, because the initial dataset doesn't perform the DML operation itself. If you don't want to see the latter count, turn NOCOUNT to ON. This, of course, means that if fewer rows are effected in your TRIGGER, you won't know about it in the output from SSMS (but it's just informational anyway).

It is also heavily advised that you don't use ROLLBACK inside a TRIGGER, handle transactions outside the TRIGGER, not inside. RAISERROR isn't recommend either and you should be using THROW for new development work (that's been recommended since 2012!). This results in a TRIGGER like below:

CREATE OR ALTER TRIGGER MyTrigger ON dbo.MyTable INSTEAD OF DELETE AS 
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM deleted WHERE SomeVal = 'Nonsense')
        THROW 95302, N'Error Msg', 16; --Use an error number appropriate for you
    ELSE
        DELETE MT FROM dbo.MyTable MT JOIN deleted ON MT.id = deleted.id;
END;
GO

Delete Records Within Instead of Delete Trigger