CODEWITHSUNDEEP
sqldatabasesql-server-2008
Zoya Sheikh
Zoya Sheikh

Reputation: 939

Count Rows Error in Trigger

I wrote this trigger below. it is generating Error at COUNT(*) From. I want that when any row inserts into table 'Users' with this trigger currently present folders in Assignments should assign to User.enter image description here

For Example: I add new row to table Users suppose userD. Then with the help of this trigger Present folders like folderA, folderB, folderC should assign to userD with folder right Visible by default. I have written this Trigger below but it is givig Error at Count(*) From

CREATE TRIGGER Trigger_Insert ON Users
FOR INSERT
AS
    declare @userid int;
    declare @username nvarchar(50);
    declare @useremail nvarchar(50);
    declare @userpassword nvarchar(50);

    select @userid=i.user_id from inserted i;   
    select @username=i.user_name from inserted i;   
    select @useremail=i.user_email from inserted i; 
    select @userpassword=i.user_password from inserted i;
    DECLARE @intFlag INT
    SET @intFlag =1
    WHILE (@intFlag <=COUNT(*) FROM Assignments;)  // Error Here
    BEGIN
       insert into UAssignment_Rights(
       ua_rights_id,ua_rights_name,assignment_id,user_id) 
       values(@userid,'Visible','','');
       SET @intFlag = @intFlag + 1
       PRINT 'AFTER INSERT trigger fired.'
    END 
GO

Can you please help me to solve this issue.

Upvotes: 0

Views: 188

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Answer updated to comply with info from comments.

My point is that most of the times you do not need loops or cursor in Sql Server. Usually set based approach is simpler and faster. In this case you might use form of insert that does not insert fixed values but rather result of select.

CREATE TRIGGER Trigger_Insert ON Users
FOR INSERT
AS
-- this is mandatory in trigger - you do not want @@rowcount reported to applications
-- to change as a result of statement in trigger (all of them, including set and select)

    set nocount on

-- You can simply take assignment_id directly from Assignments table
    insert into UAssignment_Rights(ua_rights_name, assignment_id, user_id)
    select 'Visible', a.assignment_id, i.user_id
      from Inserted i
     cross join dbo.Assignments a

    PRINT 'AFTER INSERT trigger fired.'
GO

P.S. cartesian product is result of join without join condition. Meaning that returned rows are all possible cobinations of each row from left table with each row from right table.

Upvotes: 3

freefaller
freefaller

Reputation: 19963

COUNT(*) should only be used as part of a SELECT or HAVING statement, see MSDN for more information on aggregate functions.

If Assignments will not change as part of the WHILE loop, try replacing...

WHILE (@intFlag <=COUNT(*) FROM Assignments;)  // Error Here

With...

DECLARE @AssignmentsCount INT
SELECT @AssignmentsCount = COUNT(*) FROM Assignments
WHILE @intFlag <= @AssignmentsCount

This means that the COUNT(*) is only done once.

However, if the number of Assignments could change during the loop, the replace it with...

WHILE @intFlag <= (SELECT COUNT(*) FROM Assignments)

Upvotes: 2

Related Questions