sql-server - 子请求返回了几个值

Question

这是我的代码：

CREATE TABLE IF NOT EXISTS Artfacture 
(
     N int(6) unsigned NOT NULL,
     Code varchar(50) NOT NULL,
     Ht Numeric NOT NULL 
)

INSERT INTO Artfacture (N, Code, Ht) 
VALUES ('1', '1', '10'), ('1', '2', '20'),('1', '3', '30');

CREATE TABLE IF NOT EXISTS Facture 
(
     N int(6) unsigned NOT NULL,
     Ht Numeric NOT NULL 
)

INSERT INTO Facture (N, Ht) 
VALUES ('1', '60');

CREATE TABLE IF NOT EXISTS Article 
(
     Code varchar(50) NOT NULL, 
     Famille varchar(50) NOT NULL 
)

INSERT INTO Article (Code, Famille) 
VALUES ('1', 'F1'), ('2', 'F2'), ('3', 'F3');

CREATE TABLE IF NOT EXISTS Farticle 
(
     Designation varchar(50) NOT NULL, 
     Compte varchar(50) NOT NULL 
)

INSERT INTO Farticle (Designation, Compte)  
VALUES ('F1', '700000'), ('F2', '710000'), ('F3', '720000');

CREATE TABLE IF NOT EXISTS Ecritures 
(
     Date Date,
     Pièce varchar(50) NOt NULL,
     Compte varchar(50) NOT NULL, 
     Aux varchar(50),
     Libelle varchar(100) NOT NULL,
     Débit numeric NOT NULL
     Crédit numeric NOT NULL  
)

我的请求是一个触发器，其中添加、更新或删除 table 中的一些新行Facture，它必须添加、更新、删除Ecritures具有相同行数的行Artfacture

CREATE TRIGGER [dbo].[tr_Facture] 
ON [dbo].[Facture]
FOR INSERT, UPDATE, DELETE 
AS
     DECLARE @dat AS date,
             @piece AS nvarchar(10),
             @piece1 AS nvarchar(10),
             @de money, @cr money, 
             @code AS nvarchar(255),
             @compte AS nvarchar(50),
             @Aux AS nvarchar(50),
             @lib AS nvarchar(50),
             @Famille AS nvarchar(255)

     SET @de = 0
     SET @Aux = ''
     SET @piece = (SELECT [N] FROM inserted)
     SET @piece1= (SELECT [N] FROM deleted)
     SET @dat = (SELECT [Date] FROM [dbo].[Facture] WHERE [N] = @piece)
     SET @lib = 'FACTURE DE VENTE - ' + (SELECT [Societe] FROM [dbo].[Facture] WHERE [N] = @piece)
     SET @Compte = (SELECT F.CompteV FROM Artfacture AF 
                    INNER JOIN Article A ON A.Code = AF.Code 
                    INNER JOIN Farticle F ON F.Designation = A.Famille 
                    WHERE N = @piece)
     set @Cr = (select AF.Ht from Artfacture AF inner join Article A on A.Code = AF.Code where N = @piece)
     begin
     IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted )
     insert into F_Ecritures (Date,Piece,Compte,Auxiliere,Libelle,Débit,Crédit)
     select @dat,@piece,F.CompteV,@Aux,@lib,@de, AF.Ht
     from Artfacture AF
     inner join F_Article A on A.Code = AF.Code
     inner join F_Farticle F on F.Designation = A.Famille
     where N = @piece
     group by F.CompteV, AF.Ht;
     ELSE IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
     update Ecritures set                            Date=@dat,Piece=@piece,Compte=@compte,Auxiliere=@Aux,Libelle=@lib,Débit=@de,Crédit=@cr where Piece=@piece          and Compte=@Compte
     ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

         DELETE FROM [dbo].[Ecritures] WHERE Piece = @piece1
     end

问题是我收到一个错误，即子请求返回了几个值.....

谁能帮我？

标签： sql-servertriggers

Answer 1

由于触发器在整个集合上运行，因此您需要像这样构造它。逻辑可能与您尝试做的不完全匹配，但您应该能够使用此模式获得想法。

update Ecritures set <column expressions>
from inserted
join ECritures on eCritures.Piece=inserted.N

insert ECritures(<column list>)
select <columnist>
from inserted 
where not exists(select * from deleted where deleted.Piece=inserted.N)

delete eCritures 
from eCritures
join deleted on eCritures.Piece=deleted.N
where not exists(select * from inserted where inserted.N=deleted.N)