Manual insert correct but not via T-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Manual insert correct but not via T-SQL

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-06 : 23:27:55

I create a TRIGGER instead of INSERT which does this:

quote:

DECLARE @lastAccessValueFlag tinyint
SET @lastAccessValueFlag = (SELECT TOP 1 [UserWebLevelAccessValue]
FROM UserWebAccessLevelSetting
ORDER BY ID DESC)
IF @lastAccessValueFlag IS NULL
SET @lastAccessValueFlag = 1
ELSE
SET @lastAccessValueFlag = @lastAccessValueFlag * 2

INSERT INTO UserWebAccessLevelSetting
SELECT UserWebLevelAccessName, @lastAccessValueFlag
FROM inserted

Then when you are opening up the table and manually inserting the data, the trigger works great. it multiplies the previous value found by 2 - exactly what I want.

However, if you do this:

quote:

INSERT INTO UserWebAccessLevelSetting (UserWebLevelAccessName)
SELECT DISTINCT UserWebAccessLevel
FROM MProUsersList
GROUP BY UserWebAccessLevel
ORDER BY UserWebAccessLevel ASC
GO

It does not work and only inserts the value "1", since the table is empty at this point.

any ideas? am I missing something?

I want to be able to bulk insert from one table into this new one and correctly get the trigger to insert per row.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-07 : 05:43:37

Can't use variables if you want to act on more than one row.
Your trigger will be fired once for the insert statement.
You probably also need to use the inserted table to act on what was inserted.

Can you give an example of what you want.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-07 : 06:45:10

well, the example is above :)

so when a record is inserted, I want to get the last value for the column "UserWebLevelAccessValue" (order by ID DESC) and multiply that by 2.
if this is the first record, then the default value should be 1.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-07 : 07:21:01

Do you want to do this for each record inserted? Your trigger will only fire once for the insert statement. When the table is empty the rows inserted will be the first rows so you will get the default of 1 for each row.
Note - there is no ordering to the rows so it is meaningles to talk about the first row inserted in a single statement.
You can generate an artificial value to sequence them using row_number() - something like

declare @i int
select @i = max(lastAccessValueFlag)
from UserWebAccessLevelSetting
select @i = coalesce(@1,1)
;with cte as
(select UserWebLevelAccessName, i = row_number() over (order by id desc) from inserted)
insert UserWebAccessLevelSetting
SELECT UserWebLevelAccessName, @i*2*i from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources