SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Manual insert correct but not via T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

127 Posts

Posted - 01/06/2013 :  23:27:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  05:43:37  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

tech_1
Posting Yak Master

127 Posts

Posted - 01/07/2013 :  06:45:10  Show Profile  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  07:21:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000