Tag Info

New answers tagged

0

You can utilize the returning keyword which will return the value of the created serial column. If you wrap you Insert in a With expression, you can then access the returned id and insert into the second table. WITH getval(id) as (INSERT INTO table_a (some_col) VALUES (some_val) RETURNING id) INSERT into table_b (id) SELECT id from getval; where id ...


0

in MySQL you can create a trigger. I tried on my db and it works like a champ. CREATE TRIGGER update_id_on_b AFTER INSERT ON a FOR EACH ROW INSERT INTO b SET a_id = NEW.id; INSERT INTO a VALUES(148,'test 1'); SELECT * FROM b;


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 ...


0

Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation: A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. Could you move the admin field to a separate table which just holds the current ...


0

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well. ALTER TABLE sales_details ADD FOREIGN KEY fk_product_code(product_code) REFERENCES products(product_code) ON DELETE CASCADE; But if you'd like to use a trigger to delete ...


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 ...


0

I think I solve it successfully in the easiest way: CREATE TRIGGER [dbo].[LotController] ON [dbo].[Store] AFTER DELETE AS BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debug]') AND type in (N'U')) BEGIN SET NOCOUNT ON; INSERT INTO [Debug](data,value) VALUES ('Time',GetDate()) END --Table ...


-1

4. or you can create an IDENTITY field CREATE TABLE LYNX.TRANS_AUDIT ( TA_ID **INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL**, TA_KEY_VALUE VARCHAR(100), TA_TABLE_CHANGED VARCHAR(40), TA_FIELD_CHANGED VARCHAR(40), TA_OLD_FIELD_VALUE VARCHAR(100), TA_NEW_FIELD_VALUE VARCHAR(100), TA_USER_WHO_CHANGED VARCHAR(128), TA_DATE_CHANGED TIMESTAMP, ...


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

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 ...


0

A pure SSIS solution would be to deliver the Data Flows for the Insert and Update tasks into Cache Transformations. http://msdn.microsoft.com/en-us/library/bb895264.aspx If they occur in 1 Data Flow then you might be able to send them to a single Cache, otherwise you will need 2. Then you can test against those Caches in later Data Flows using the ...


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 ...


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 ...


0

You forgot END // DROP TRIGGER IF EXISTS `init_uuid_company`// CREATE TRIGGER `init_uuid_company` BEFORE INSERT ON `company` FOR EACH ROW BEGIN IF(NEW.uuid IS NULL OR NEW.uuid = '') THEN SET NEW.uuid = UUID(); END IF; END //


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 ...


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(); ...



Top 50 recent answers are included