Reputation: 12134
Is it possible to have a non-null column where the value is generated at insert by calling a stored procedure the parameters of which are values passed to insert into the row?
For example, I have table User
:
| username | name | surname | id |
Insert looks like this:
INSERT INTO USER (username, name, surname)
VALUES ('myusername', 'myname', 'mysurname');
The id
column is populated with an (integer) value retrieved by calling stored procedure mystoredproc
with parameters myusername
, myname
, mysurname
.
A further question is, would this stored procedure be called on each row, or can it be called in a grouped fashion. For example, I'd like my stored procedure to take the name
and append a random integer to it so that that if I insert 100 users with the name 'David', they will get the same id
and the stored procedure will be called only once. A bit of a bad example on the second point.
Upvotes: 0
Views: 189
Reputation: 2434
Good day,
Is it possible to have a non-null column where the value is generated at insert by calling a stored procedure
Option 1: please check if this work for you
** there is no need to use external SP probably, but you can execute SP from trigger if needed
** All executed by a trigger is in the same transaction as the original query.
would this stored procedure be called on each row
NO! The trigger will be executed once for all rows you insert in the same statement. The inserted table includes all the rows which were inserted. In your update section (step 4) you can update all the rows which were inserted in once and no need to execute something for each row
** If you do use external SP which is executed from the trigger then you can pass it all the inserted table as one using Table-Valued Parameter
------------------- update ---------------
Here is a full example of using this logic:
drop table if exists T;
CREATE TABLE T (id int identity(2,2), c int NOT NULL default 1)
GO
CREATE TRIGGER tr ON T AFTER INSERT
AS BEGIN
SET NOCOUNT ON;
UPDATE T SET T.c = T2.C + 1
FROM inserted T2
INNER JOIN T T1 ON T1.id = T2.id
END
INSERT T(c) values (1) -- I insert the value 1 but the trigger will change it to 1+1=2
select * from T
GO
-- test multiple rows:
INSERT T(c) values (10),(20),(30),(40)
select * from T
GO
Upvotes: 2
Reputation: 94
DECLARE @rc INT = 0,
@UserID INT = ABS(CHECKSUM(NEWID())) % 1000000 + 1;
WHILE @rc = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE UserId= @UserId)
BEGIN
INSERT dbo.Users(UserId) WHERE Username = @UserName SELECT @UserId;
SET @rc = 1;
END
ELSE
BEGIN
SELECT @UserId = ABS(CHECKSUM(NEWID())) % 1000000 + 1,
@rc = 0;
END
END
Upvotes: -1