Hot answers tagged

7

As long as the trigger is only for insert/delete: IF EXISTS (SELECT 1 FROM inserted) -- I am an insert ELSE -- I am a delete And in fact if you are doing things based on a join to inserted/deleted, it can be even simpler: -- do something for an insert -- this will only actually do anything when it's an insert INSERT dbo.somewhereElse SELECT ...


4

The Question mentions that the "code is failing" but there is no indication of any error message or what specifically is failing. Including at least one, if not both, of those pieces of information always helps get better answers. For the moment, I see something that appears to be an incorrect assumption about Triggers and Transactions: you increment the ...


3

A trigger is already always operating within an implicit transaction, see the related question: Is there a way to ensure that a SQL Server trigger will be executed? You can catch an error and prevent an abort/rollback, but you cannot "commit" the transaction from within the trigger.


3

TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them. That's what the process list is showing. There is a (transaction with a) delete query that is running and has some lock on the table and is blocking the TRUNCATE ...


2

If I write it out like this @OldValue = (select deleted.ID from #del) I get the correct value. You only get the correct value if there is a single row being updated or deleted. But Triggers in SQL Server are fired once per DML operation, and the inserted and deleted pseudo-tables contain all of the rows per that operation. So if there were 2 or more ...


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


1

You can use this format for copying your original values over: CREATE OR REPLACE TRIGGER update_Trigger BEFORE UPDATE ON customer FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO replica VALUES(:OLD.cID, :OLD.cName, ... ); END IF; END; / You will need to specify each field you want inserted into replica This will be slow and cause a lot ...


1

I do not think that the check is needed on delete. So create a trigger like: CREATE OR REPLACE TRIGGER check_session_timing BEFORE INSERT OR UPDATE ON sessions FOR EACH ROW BEGIN if :new.SessionDay = 'Saturday' or :new.SessionDay = 'Sunday' then raise_application_error(-20001, 'Out of business hours – this transaction has not ...


1

You should take the vbalues of OLD and use them in INSERT command. Something like this: CREATE TRIGGER triger_name_after_delete AFTER DELETE ON resident FOR EACH ROW BEGIN -- Insert record into Outpatient table INSERT INTO Outpatient (residentID, [rest columns names...] ) VALUES ( OLD.residentID, OLD.[rest columns names...]) END;


1

Have you considered using a trace for that? https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx You would be able to configure it for specific tables and store output in a table. I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table. In some situations ...


1

The trigger always "runs" -- but just because it runs doesn't mean it has to perform any action. And a trigger that doesn't perform any action doesn't take very long, if you're concerned about efficiency. In your case, all you need is logic to compare NEW.SellingPrice to OLD.SellingPrice and only perform the INSERT action if the two values are not the ...


1

Since my comment went unanswered, I have converted it to an answer. I want to do some work if it's an insert and some other work if it is a delete. You could create separate triggers, one for insert, and one for delete: CREATE TRIGGER dbo.Table1_AI ON dbo.Table1 AFTER INSERT AS ... insert-specific actions CREATE TRIGGER dbo.Table1_AD ON dbo.Table1 ...


1

Yes, you can do this inside a trigger. A trigger can reference the objects INSERTED and DELETED which can be used as tables to tell what information is inserted and deleted. When an update is made, the old rows are "deleted" and the new rows are "inserted." Write your SQL statement to INSERT all the rows from INSERTED and DELETED into your log table. I ...


1

try this - you might get the best of both select manyfield from ( select manyfield , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum from inserted left join sometable t1 on inserted.id = t1.id ) tt where rownum = 1 or t1.id is null



Only top voted, non community-wiki answers of a minimum length are eligible