Good day,
I am relatively new to triggers. I thought I new what I was doing (first mistake) until an update trigger contained more than 1 row.
SQL Server Triggers are different than a lot of other RDBMS. In Oracle, for instance, you would do just as you have done except your would add 'FOR EACH ROW' and the RBAR you wrote would work just. SHOW CREATE TRIGGER Syntax. SHOW CREATE USER Syntax. SHOW CREATE VIEW Syntax. SHOW DATABASES Syntax. ON DUPLICATE KEY UPDATE Syntaxā€¯. In MySQL 8.0, the DELAYED keyword is accepted but ignored by the server. For the reasons for this. INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of. When you insert/update multiple rows into a table, the Inserted temporary table used by the system holds all of the values from all of the rows that were inserted or updated. Therefore, if you do an update to 6 rows, the Inserted table will also have 6 rows, and doing something like this: SET @Candidate_Post_ID = (Select ID From inserted).
Mysql Trigger Update Multiple Rows Sql
This is for a system that I did not design but inherited. I need to make the minimum changes necessary.
For historical reasons whenever an invoice is created a duplicate of the vendor records is created (and the duplicates are not visible in vendor dropdowns). Currently, when the user changes the vendor code to the visible record (the original) it is no longer linked to the copies and searching for invoices for the new vendor code will not reveal any old data.
So I added 2 columns, OrigVenCode and OrigVenID to setup the relationship. (I don't know if the origvencode will ever be of use but I want to retain it just in case)
Table layout (last 2 columns are new)
vendorID VendorCode IsCopy OrigVenCode OrigVenID
----------- ------------ ---------- ------------- -----------
1 ABC 0 ABC 1
2 ABC 1 ABC 1
3 ABC 1 ABC 1
4 DEF 0 HHH 4 3 edition mazda touring cx5.
5 DEF 1 HHH 4
Sql Server Trigger On Insert
So I need a trigger (update?) that when the vendor code of the original record (indicated by IsCopy = 0) is changed that all the copies are changed as well.
For instance if I edit vendorID 1 and set the vendorcode = xxx I want the vendorcode for vendorID 2 and vendorID 3 changed as well.
Too me this looked very easy until the case where multiple records are modified within a transaction and I get the beloved:
'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.'
error. How do I take into account multiple rows in the update?
Here is my trigger:
CREATE TRIGGER Trig_vendor_code ON [vendor] FOR UPDATE
AS
DECLARE @newcode varchar(10)
DECLARE @venid int
DECLARE @iscopy int
SELECT @venid = (SELECT vendorID FROM Inserted)
SELECT @iscopy = (SELECT IsCopy FROM Inserted)
--only care if we are working on an original
IF (@iscopy = 1)
BEGIN
RETURN
END
--if vendor.code wasn't update, exit
IF NOT UPDATE(vendorCode)
BEGIN
RETURN
![Records Records](https://i1.wp.com/www.tech-recipes.com/wp-content/uploads/2013/05/Coding-After-insert-trigger-in-sql-server-2.png?resize=608%2C400)
END
SELECT @newcode = (SELECT vendorCode FROM Inserted)
UPDATE vendor SET vendorcode = @newcode WHERE origvenid = @venid and IsCopy = 1
Any help would be much appreciated. I hope you understand my example. And sorry, I did not design the messy structure. Just trying to prevent further data loss.
-Markus