StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow create INSTEAD OF INSERT trigger
text
Body
copied!<p>I create trigger </p> <pre><code>CREATE TRIGGER PartnersTrigger on Partners INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON Declare @Key nvarchar(10); EXEC @Key = sp_GeneratePassword 5; UPDATE Partners SET KeyInvitation = @Key WHERE Id IN (SELECT Id FROM inserted); INSERT INTO Partners(Email,KeyInvitation) SELECT Email, KeyInvitation FROM inserted END GO </code></pre> <p>But I need set to <code>KeyInvitation</code> variable <code>@Key</code>. And set <code>sp_GeneratePassword 5</code> (procedure) in <code>@Key</code>.</p> <p>How to do this?</p> <p><strong>UPDATE</strong></p> <pre><code>CREATE PROCEDURE sp_GeneratePassword ( @Length int ) AS DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN SET @RandomNumber = Rand() SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1)) SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) SET @counter = @counter + 1 SET @RandomID = @RandomID + @CurrentCharacter END SELECT @RandomID AS 'Password' GO </code></pre> <p>this not work:</p> <pre><code>Declare @Key nvar char(10); -- can't post when I use "nvarchar" here EXEC @Key = sp_GeneratePassword 5; </code></pre> <p>Error:</p> <blockquote> <p>Incorrect syntax near 'char'. Must declare the scalar variable "@Key". Must declare the scalar variable "@Key". Must declare the scalar variable "@Key".</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload