problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
----
--
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com