Tag Info

Hot answers tagged

7

This is the expected behaviour of a statement trigger. It executes every time a statement (INSERT in this case) is executed and once (per statement, not per row.) The behaviour is similar in other DBMS (like Oracle.) The MSDN says it in the first paragraph in the CREATE TRIGGER page: These triggers fire when any valid event is fired, regardless of ...


2

How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins. Use the following for your OLE DB Source UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0; That updates everything in a nice atomic ...


2

Since you are using SSIS and capturing the changed rows, you should have SSIS capture the PK values for the rows that are being processed. The last step of the package should be to do a targeted update on the source table, setting the [Archive] field to 1 based on those PK values. And if not already done, the update trigger needs to exclude those changes ...


2

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT. Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT: The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the ...


2

If I'm reading that right, this should work: select from deleted d where not exists (select 1 from inserted i where i.a=d.a and i.b=d.b) Regarding srutzky's comment "And for an UPDATE, both inserted and deleted tables have the exact same rows in them (as you cannot update a row that did not exist)." This is not true in the case of updatable ...


1

If you have no other code in the trigger that should be allowed to cancel the INSERT, then try the following: Keep the XACT_ABORT OFF Put the COMMIT TRAN back Add a BEGIN TRAN at the end of the trigger This should be the same issue as if you have an open transaction, exec a proc, and in that proc do a ROLLBACK (or even just a BEGIN TRAN and no COMMIT or ...


1

What you are looking for is to actually do the INSERT into warehouse_maine after Warehouse DELIMITER $$ DROP TRIGGER IF EXISTS Warehouse_InsertAfter $$ CREATE TRIGGER Warehouse_InsertAfter AFTER INSERT ON Warehouse FOR EACH ROW BEGIN IF NEW.state = 'maine' THEN INSERT IGNORE INTO warehouse_maine (shipperid, name, state) VALUES ...


1

Okay I figured it out. No Need for Delimiters because it is technically one line or one statement and only needs one semicolon. Also make sure to notice that one is for before an Insert and one before an Update. I believe that SET was also necessary. CREATE TRIGGER CREATE_BY_MENU_TR BEFORE INSERT ON MENU FOR EACH ROW SET NEW.Create_By = CURRENT_USER(); ...


1

Change Tracking (CT) and Change Data Capture (CDC) are built into SQL Server. CT can identify the rows which changed since you last asked. CDC can provide a complete history of all data changes since you last asked. Both are easily manipulated from SSIS. There will be no need to reset flags per row after processing. The system handles all this through ...



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