Trigger not firing for instead of insert SQL Server-CodePudding

I am trying to create a trigger to convert a value from varchar to varbinary.

The creation of the trigger is successful. However it is not firing against an insert query.

Trigger code:

CREATE TRIGGER HashPassword
ON Users
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Users (Username, [Password], FirstName, LastName, RoleID)
        SELECT
            i.Username,
            CONVERT(varbinary(max), EncryptByPassPhrase('iskam_6_za_bazata_moje_i_za_springa', i.[Password]), 2),
            i.FirstName, i.LastName, i.RoleID
        FROM 
            inserted AS i
END

Users table:

CREATE TABLE [dbo].[Users]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Username] [varchar](255) NOT NULL,
    [Password] [varbinary](max) NOT NULL,
    [FirstName] [nvarchar](255) NOT NULL,
    [LastName] [nvarchar](255) NOT NULL,
    [RoleID] [int] NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] WITH CHECK 
    ADD CONSTRAINT [FK_Users.RoleID] 
        FOREIGN KEY([RoleID]) REFERENCES [dbo].[Roles] ([Id])
GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users.RoleID]
GO

I tried executing this query:

INSERT INTO Users (Username, [Password], FirstName, LastName, RoleID)
VALUES ('martini', 'parola', 'martin', 'atanasov', 4)

But the result was an error:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

CodePudding user response：

You can't insert a varchar into a varbinary max column. So this

INSERT INTO Users(Username,[Password],FirstName,LastName,RoleID)
VALUES('martini','parola','martin','atanasov',4)

should be

INSERT INTO Users(Username,[Password],FirstName,LastName,RoleID)
VALUES('martini', cast(N'parola' as varbinary(max)),'martin','atanasov',4)

Or you create a view on Users that performs the varbinary>nvarchar cast and put your instead of trigger on that.