CODEWITHSUNDEEP
sql-servertriggerssql-server-2012
J.Fernandes
J.Fernandes

Reputation: 87

Sql Trigger on a view does not getting triggered

I have a view getting data from others views when data is inserted in this specific view as show bellow need to insert in table vendas ref(nvarchar(50)) and Estado(bool). 

ALTER TRIGGER [dbo].[TGR_VENDAS] 
ON [dbo].[VendasFinal]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE
    @Ref  nvarchar(50),
@EstadoString nvarchar(50)

SELECT @Ref = i.ref, @EstadoString = i.ESTADO 
FROM inserted i

if(@EstadoString = 'Em Aberto')
    BEGIN
        INSERT INTO dbo.Vendas
            (ref, Estado)
        VALUES
            (@Ref ,0);
    END
ELSE
    BEGIN
        INSERT INTO dbo.Vendas
            (ref, Estado)
        VALUES
            (@Ref ,1);
    END
END

It's running on a MS Sql Server 11.0. Thanks in advance.

Upvotes: 1

Views: 93

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

You don't need a cursor here. You just need to use a case expression. Your posted trigger can be simplified to this. It handles any number of rows and the logic you have in the existing code.

ALTER TRIGGER [dbo].[TGR_VENDAS] 
ON [dbo].[VendasFinal]
INSTEAD OF INSERT
AS
BEGIN

     INSERT INTO dbo.Vendas
    (
        ref
        , Estado
    )
    SELECT i.ref
        , case when i.ESTADO = 'Em Aberto' then 0 else 1 end
    from inserted i

Upvotes: 1

Related Questions