我正在嘗試創建一個觸發器來將值從 varchar 轉換為 varbinary。
觸發器創建成功。但是，它不會觸發插入查詢。
觸發代碼：
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桌子：
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
我嘗試執行此查詢：
INSERT INTO Users (Username, [Password], FirstName, LastName, RoleID)
VALUES ('martini', 'parola', 'martin', 'atanasov', 4)
但結果是錯誤的：
不允許從資料型別 varchar 到 varbinary(max) 的隱式轉換。使用 CONVERT 函式運行此查詢。
uj5u.com熱心網友回復：
您不能將 varchar 插入到 varbinary max 列中。所以這
INSERT INTO Users(Username,[Password],FirstName,LastName,RoleID)
VALUES('martini','parola','martin','atanasov',4)
應該
INSERT INTO Users(Username,[Password],FirstName,LastName,RoleID)
VALUES('martini', cast(N'parola' as varbinary(max)),'martin','atanasov',4)
或者您在用戶上創建一個執行 varbinary>nvarchar 轉換的視圖，并將您的 instead of 觸發器放在上面。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/536640.html
標籤：数据库sql服务器触发器