multiple inserted

I am writing a trigger on one table that has dependency on what is inserted
on a different table. so how can i grab the info that is inserted in one
table to use in a query in another trigger.
thanks.seeker53 wrote:
> I am writing a trigger on one table that has dependency on what is
> inserted on a different table. so how can i grab the info that is
> inserted in one table to use in a query in another trigger.
> thanks.
Use the "inserted" virtual table from the insert trigger. It contains
all the rows that were inserted in the table in a DDL format that
matches the underlying table.
--
David Gugick
Imceda Software
www.imceda.com|||Here is the existing trigger:
if exists(select count(b.begin_datetime)
from inserted a inner join tblevent b on a.event_sysid =b.event_sysid
inner join tblparticipating_entity c on a.entity_id =c.entity_id
where a.entity_type_code = 'c'
group by a.entity_type_code, b.begin_datetime
having count(b.begin_datetime) >1 and b.begin_datetime >='1/14/05')
begin
select @.errno = 30080,
@.errmsg = 'begin_datetime: Appointments can not be same day dated.'
goto error
end
this inserted will only give data inserted into the tblparticipating_entity
table but in the above query I also need the inserted value of begin_datetime
which is in the tblevent. As this trigger stands now data exists and so the
trigger is fired but I need the query to filter down to the inserted consumer
which is in the participating_entity inserted virtual table and also the
inserted begin_datetime which is in the tblevent inserted virtual table.
What I want the trigger to do is give an alert to a scheduler that a
consumer can not have more than one appointment in a day. Thanks.
"David Gugick" wrote:
> seeker53 wrote:
> > I am writing a trigger on one table that has dependency on what is
> > inserted on a different table. so how can i grab the info that is
> > inserted in one table to use in a query in another trigger.
> >
> > thanks.
> Use the "inserted" virtual table from the insert trigger. It contains
> all the rows that were inserted in the table in a DDL format that
> matches the underlying table.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||On Fri, 14 Jan 2005 05:53:15 -0800, seeker53 wrote:
>Here is the existing trigger:
>if exists(select count(b.begin_datetime)
> from inserted a inner join tblevent b on a.event_sysid =>b.event_sysid
> inner join tblparticipating_entity c on a.entity_id =>c.entity_id
> where a.entity_type_code = 'c'
> group by a.entity_type_code, b.begin_datetime
> having count(b.begin_datetime) >1 and b.begin_datetime >=>'1/14/05')
> begin
> select @.errno = 30080,
> @.errmsg = 'begin_datetime: Appointments can not be same day dated.'
> goto error
> end
Hi seeker53,
This trigger will always show the error message, regardless of the data
you actually tried to insert or update.
The subquery calculates a COUNT. If no single row matches the WHERE and
HAVING clauses of the subquery, the result of COUNT(..) will be 0 (the
number zero, not the letter ooh, nor the "absence symbol" NULL). So the
subquery will return 1 row (with the value 0 as data) and the exists
clause will be evaulated as true.
Another problem with your trigger code is that the date format 1/14/05 is
ambiguous. The only safe format for dates is yyyymmdd (20050114). Oh, and
hard coding todays date is not really a best practice either - unless you
really have a strong desire to go and manually edit this date every day
from now on!
I'd like to give you some suggestions how to rewrite this trigger, but to
tell you the truth, I have no idea what you're trying to accomplish, how
your tables look, etc. Please have a look at www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

multiple inserted

I am writing a trigger on one table that has dependency on what is inserted
on a different table. so how can i grab the info that is inserted in one
table to use in a query in another trigger.
thanks.
seeker53 wrote:
> I am writing a trigger on one table that has dependency on what is
> inserted on a different table. so how can i grab the info that is
> inserted in one table to use in a query in another trigger.
> thanks.
Use the "inserted" virtual table from the insert trigger. It contains
all the rows that were inserted in the table in a DDL format that
matches the underlying table.
David Gugick
Imceda Software
www.imceda.com
|||Here is the existing trigger:
if exists(select count(b.begin_datetime)
from inserted a inner join tblevent b on a.event_sysid =
b.event_sysid
inner join tblparticipating_entity c on a.entity_id =
c.entity_id
where a.entity_type_code = 'c'
group by a.entity_type_code, b.begin_datetime
having count(b.begin_datetime) >1 and b.begin_datetime >=
'1/14/05')
begin
select @.errno = 30080,
@.errmsg = 'begin_datetime: Appointments can not be same day dated.'
goto error
end
this inserted will only give data inserted into the tblparticipating_entity
table but in the above query I also need the inserted value of begin_datetime
which is in the tblevent. As this trigger stands now data exists and so the
trigger is fired but I need the query to filter down to the inserted consumer
which is in the participating_entity inserted virtual table and also the
inserted begin_datetime which is in the tblevent inserted virtual table.
What I want the trigger to do is give an alert to a scheduler that a
consumer can not have more than one appointment in a day. Thanks.
"David Gugick" wrote:

> seeker53 wrote:
> Use the "inserted" virtual table from the insert trigger. It contains
> all the rows that were inserted in the table in a DDL format that
> matches the underlying table.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Fri, 14 Jan 2005 05:53:15 -0800, seeker53 wrote:

>Here is the existing trigger:
>if exists(select count(b.begin_datetime)
> from inserted a inner join tblevent b on a.event_sysid =
>b.event_sysid
> inner join tblparticipating_entity c on a.entity_id =
>c.entity_id
> where a.entity_type_code = 'c'
> group by a.entity_type_code, b.begin_datetime
> having count(b.begin_datetime) >1 and b.begin_datetime >=
>'1/14/05')
> begin
> select @.errno = 30080,
> @.errmsg = 'begin_datetime: Appointments can not be same day dated.'
> goto error
> end
Hi seeker53,
This trigger will always show the error message, regardless of the data
you actually tried to insert or update.
The subquery calculates a COUNT. If no single row matches the WHERE and
HAVING clauses of the subquery, the result of COUNT(..) will be 0 (the
number zero, not the letter ooh, nor the "absence symbol" NULL). So the
subquery will return 1 row (with the value 0 as data) and the exists
clause will be evaulated as true.
Another problem with your trigger code is that the date format 1/14/05 is
ambiguous. The only safe format for dates is yyyymmdd (20050114). Oh, and
hard coding todays date is not really a best practice either - unless you
really have a strong desire to go and manually edit this date every day
from now on!
I'd like to give you some suggestions how to rewrite this trigger, but to
tell you the truth, I have no idea what you're trying to accomplish, how
your tables look, etc. Please have a look at www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

multiple inserted

I am writing a trigger on one table that has dependency on what is inserted
on a different table. so how can i grab the info that is inserted in one
table to use in a query in another trigger.
thanks.seeker53 wrote:
> I am writing a trigger on one table that has dependency on what is
> inserted on a different table. so how can i grab the info that is
> inserted in one table to use in a query in another trigger.
> thanks.
Use the "inserted" virtual table from the insert trigger. It contains
all the rows that were inserted in the table in a DDL format that
matches the underlying table.
David Gugick
Imceda Software
www.imceda.com|||Here is the existing trigger:
if exists(select count(b.begin_datetime)
from inserted a inner join tblevent b on a.event_sysid =
b.event_sysid
inner join tblparticipating_entity c on a.entity_id =
c.entity_id
where a.entity_type_code = 'c'
group by a.entity_type_code, b.begin_datetime
having count(b.begin_datetime) >1 and b.begin_datetime >=
'1/14/05')
begin
select @.errno = 30080,
@.errmsg = 'begin_datetime: Appointments can not be same day dated.'
goto error
end
this inserted will only give data inserted into the tblparticipating_entity
table but in the above query I also need the inserted value of begin_datetim
e
which is in the tblevent. As this trigger stands now data exists and so the
trigger is fired but I need the query to filter down to the inserted consume
r
which is in the participating_entity inserted virtual table and also the
inserted begin_datetime which is in the tblevent inserted virtual table.
What I want the trigger to do is give an alert to a scheduler that a
consumer can not have more than one appointment in a day. Thanks.
"David Gugick" wrote:

> seeker53 wrote:
> Use the "inserted" virtual table from the insert trigger. It contains
> all the rows that were inserted in the table in a DDL format that
> matches the underlying table.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||On Fri, 14 Jan 2005 05:53:15 -0800, seeker53 wrote:

>Here is the existing trigger:
>if exists(select count(b.begin_datetime)
> from inserted a inner join tblevent b on a.event_sysid =
>b.event_sysid
> inner join tblparticipating_entity c on a.entity_id =
>c.entity_id
> where a.entity_type_code = 'c'
> group by a.entity_type_code, b.begin_datetime
> having count(b.begin_datetime) >1 and b.begin_datetime >=
>'1/14/05')
> begin
> select @.errno = 30080,
> @.errmsg = 'begin_datetime: Appointments can not be same day date
d.'
> goto error
> end
Hi seeker53,
This trigger will always show the error message, regardless of the data
you actually tried to insert or update.
The subquery calculates a COUNT. If no single row matches the WHERE and
HAVING clauses of the subquery, the result of COUNT(..) will be 0 (the
number zero, not the letter ooh, nor the "absence symbol" NULL). So the
subquery will return 1 row (with the value 0 as data) and the exists
clause will be evaulated as true.
Another problem with your trigger code is that the date format 1/14/05 is
ambiguous. The only safe format for dates is yyyymmdd (20050114). Oh, and
hard coding todays date is not really a best practice either - unless you
really have a strong desire to go and manually edit this date every day
from now on!
I'd like to give you some suggestions how to rewrite this trigger, but to
tell you the truth, I have no idea what you're trying to accomplish, how
your tables look, etc. Please have a look at www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

multiple insert call for a table having insert trigger

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

ThanksLooks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||Thanks for reply me back..
I am using cursor to call multiple insert inside that curser.. here is the code..

OPEN DestinationIDList
FETCH NEXT FROM DestinationIDList INTO @.DestinationID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO Table(ID, DestinationID) VALUES(@.ID, @.DestinationID)

FETCH NEXT FROM DestinationIDList INTO @.DestinationID
END
CLOSE DestinationIDList
DEALLOCATE DestinationIDList

What should I have to do so that trigger fire for each insert ?

Thanks

Originally posted by aldo_2003
Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||What you could do is to write the Insert as a stored procedure.

Then call this proc from your cursor passing the variables that you have defined in your cursor

something like :

create proc Insert_T1
(@.id as int , @.DestinationID as int)
as

INSERT INTO T1(ID, DestinationID) VALUES(@.ID, @.DestinationID)
go

All you do call this proc in your cursor land this should make your trigger fire for every insert you perform

Give it a go and see if it works

Cheers|||No Luck :-(|||Can you post the code for your trigger

Cheers|||Here is the trigger's code

CREATE TRIGGER TR_TD
ON dbo.TRHistory FOR INSERT

AS

DECLARE @.ID int
DECLARE @.DSTID int
DECLARE @.RT decimal (18,4)
DECLARE @.Time datetime
DECLARE @.GTID int
DECLARE @.GTExist int
DECLARE @.intErrorCode INT
SET @.GTID = 0
SET @.GTExist = 0
SET @.ID = 0

-- CHECK ID' S VALIDITY

SELECT @.ID = i.ID, @.DSTID= i.DSTID,
@.RT = i.RT, @.Time = i.Time
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ID = i.ID

IF @.ID <> 0

BEGIN

-- FIND OUT NO OF GTS FOR SPECIFIC DSTS TO UPDATE FOR CORRESPONDING GT
DECLARE GTList CURSOR FOR
-- SELECT GTS FOR DSTID
SELECT GTs.GTID
FROM GTs INNER JOIN
GTDSTs ON GTs.GTID = GTDSTs.GTID INNER JOIN
ITSPs ON GTs.ID = ITSPs.ID
Where GTs.ID = @.ID AND DSTID = @.DSTID

OPEN GTList

FETCH NEXT FROM GTList INTO @.GTID

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)
BEGIN
SELECT @.intErrorCode = 1
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM

END
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0

BEGIN

-- CHECK WIEHTER GT ENTERY EXISTS IN TR TABLE
-- IF SO THEN UPDATE ELSE INTER NEW FOR GT

SELECT @.GTExist = GTid
FROM TR
WHERE GTid = @.GTid AND DSTID = @.DSTID

IF @.GTExist > 0

UPDATE TR
SET RT = @.RT,
Time = getdate()

WHERE GTID = @.GTid


ELSE

INSERT INTO TR (GTID, DSTID, RT)
VALUES (@.GTID,@.DSTID,@.RT)
-- IN CASE FOR ANY EXCEPTION GO TO PROBLEM PARA AND CLOSE & DEALLOCATE CURSOR
SELECT @.intErrorCode = @.@.ERROR
IF (@.intErrorCode <> 0)
BEGIN
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM
END
FETCH NEXT FROM GTList INTO @.GTID


END
CLOSE GTList
DEALLOCATE GTList
COMMIT TRAN
END
ELSE
-- INVALID ID
BEGIN
RAISERROR (' Invalid ID ', 16, 1)
ROLLBACK TRAN
END


PROBLEM:
IF (@.intErrorCode <> 0)
BEGIN
IF (@.intErrorCode = 1)
RAISERROR (' Insert is rejected due to invalid GT DST info', 16, 1)
ELSE
RAISERROR ('Error occured to udated info for tr' s. Please contact administrator ', @.intErrorCode, 1)
ROLLBACK TRAN


END|||Hi aldo_2003

I am Waiting.. Only problem is that if i use multiple insert with GO then multiple insert works.. but when i call is in cursor for multiple then it add only first insert..|||Just got back from lunch mate

Am goiung to try a couple if things and get back to you

Cheers|||Had a quick look at the trigger

Don't think it is the GO statement as I managed to get a test trigger to fire twice without the GO.

I'm not sure why your trigger is only firing once.

What you might want to do is to try and reduce the complexity of the code within the trigger by putting that logic within the stored proc and calling this from your trigger.

You have cursors within cursors when you take the trigger into account and this may be causing you issues that you are not aware of.

Get the trigger to fire properly without any logic in it i.e

Get the first cursor inserting into your table and the trigger firing a simple insert into a test table. Once you have that then implement your trigger logic in a stored proc and call that from the trigger.

I hope this helps, let me know how you get on.

Cheers|||Well there are just so many things...

first you don't need a cursor...collapse the cursor and the insert in to 1...

second a cursor in a trigger can't be a good idea performance wise...but like I said collapse them

third

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)

isn't a check for existance...

Look at @.@.ROWCOUNT

fourth...never mind...fixe the insert first...

Multiple Deletions From Different Tables in SQL Server Trigger

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB
6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @.AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDischarge Table
DELETE FROM tblProgramDischarge
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignment Table
DELETE FROM tblRoomAssignment
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySurvey Table
DELETE FROM tblUnitedWaySurvey
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @.AdmissionIDDoes it all not work or if you break it down into sections does it
still not work. Also how does it handle null values. When I started
using triggers comparisons with Nulls were a right pain. I take it that
all these tables are all in the same database with the same
permissions.

Ginters

bmccollum wrote:
> I have written a trigger that's supposed to go out and delete
> corresponding records from multiple tables once I delete a specific
> record from a table called tblAdmissions.
> This does not work and I'm not sure why...
> Here's the code that's supposed to run, let's say, if a user (via a
VB
> 6.0 interface) decides to delete a record. If the record in the
> tblAdmissions table has the primary key (AdmissionID) of "123", then
> the code below is supposed to search other tables that have related
> information in them and also have an AdmissionID of "123" and delete
> that information as well.
> Any ideas? Here's the code:
> CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
> -- and here is the table name
> ON tblAdmissions
> -- the operation type goes here
> FOR DELETE
> AS
> -- I just need one variable this time
> DECLARE @.AdmissionID int
> -- Now I'll make use of the deleted virtual table
> SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)
> -- And now I'll use that value to delete the data in
> -- the tblASIFollowUp Table
> DELETE FROM tblASIFollowUp
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblProgramDischarge Table
> DELETE FROM tblProgramDischarge
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblRoomAssignment Table
> DELETE FROM tblRoomAssignment
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblTOADS Table
> DELETE FROM tblTOADS
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblUnitedWaySurvey Table
> DELETE FROM tblUnitedWaySurvey
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblWFGMSurvey Table
> DELETE FROM tblWFGMSurvey
> WHERE AdmissionID = @.AdmissionID|||What does "does not work" mean? Could you be a bit more specific. Why
not use cascading deletes on foreign keys for this? See the ON DELETE
CASCASE option in Books Online for details.

Your trigger will fail to delete all related rows if more than one row
is deleted from the Admissions table. Don't write triggers that way. To
do it in a trigger, try this:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE

AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE admissionid = tblASIFollowUp.admissionid)

... etc

If you need more help, please post some code that will actually
reproduce the problem, including the CREATE, INSERT and DELETE
statements (simplified if possible please).
--
David Portas
SQL Server MVP
--|||On 31 Jan 2005 08:13:54 -0800, bmccollum wrote:

>This does not work and I'm not sure why...

Hi bmccollum,

Well, "does not work" is not exactly an accurate description of what's
happening. Is the delete rejected? Is the delete accepted, but the action
that the trigger should do is not done? Do you get error messages? Is
white smoke bellowing out of your server?

>Any ideas? Here's the code:
(snip)

Based on your code, I can do a wild guess. In fact, you've got two
problems. Both are here:

>SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

First, the second @. should be left out. This will simply set the variable
@.AdmissionID equal to itself.

But if you change it to

SELECT @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SET @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SELECT @.AdmissionID = AdmissionID FROM Deleted

you'll still have problems. Not if you delete only one row, but you'll get
an error as soon as one DELETE operation deletes more than one row from
the admissions table. It's important to know that triggers fire once per
statement, not once per row. If three rows are deleted, the deleted
pseudo-table will hold three rows. This will cause the first two versions
of the assignment to error; the third will simply assign the value from
one of these three rows to @.AdmissionID.

Even if your present application will never delete more than one row at a
time, you should always ensure that your triggers handle multi-row
inserts, updates and deletes well. Someday, your application will be
changed...

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.AdmissionID = tblASIFollowUp.AdmissionID)

(etc)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||A few things to start with. Read ISO-11179, so you will stop putting
those silly prefixes on data element names. Besides violating
standards, it makes a data dictionary almost impossible to use. The
reason I call it silly is that SQL only has one data structure, so the
prefix is redundant, improper and useless all at once.

You name a thing for that it is; you do not name a thing for how it is
modeled, where it is stored, its datatype, etc. Think logical AND NOT
physical.

Do you really have tables with only one row in them? That is what a
singular name says; tables out to be collective or plural. A table is
a set, not am object instance.

Do not depend on the use of the "little snail" to identify your
parameter to the guy maintaining or porting your code. What does it
mean in the data model?

Use "SET <var> = <exp>;" instead of "SELECT <var> = .." so that you do
not create confusion and the code will port. SQL Server has a lot of
options for standard code now, so use them.

Now the real question. Why are you still thinking of procedural code
in a declarative language, like SQL? You can use DRI (declarative
referential integrity) actions to do this. Try this skeleton:

CREATE SCHEMA Foobar ..
...
CREATE TABLE Admissions -- the source of the data element
(admission_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE ASI_Followups
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE ProgramDischarges
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE RoomAssignments
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE Toads -- weird name!
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE UnitedWaySurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE WFGMSurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

Besides being easier to code, this gives the optimizer information
about the relationships among the tables, so ALL your queries improve.
It is also faster than a TRIGGER. For example, in Sybase SQL Anywhere
there would be a single occurrence of each admission_id value and
pointer chains to all the table referencing it. Updates and deletes
are almost immediate even on huge tables.

You are still un-learning procedural code -- your "tbl-" prefixes were
a good sign that your real problem is foundations. After cleaning up
SQL code for 15-20 years, I have a good set of diagnostics :)
Go to BOL and look teh DRI you need.

Multiple Connections in Managed Trigger

Hi All,

I am trying to open multiple connections in a Managed Trigger but encoutering an error as :

System.Data.SqlClient.SqlException: Transaction context in use by another session.

Below is the sample code:

public partial class Triggers
{

[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}

Any help please ?

Thanks...

Hi Nagul!

Most probably, your trigger runs in a transaction. And I think, your second connection is a loopback connection to the same server (which creates a new session). Currently, two sessions cannot share one transaction. You can avoid the problem by placing the second connection in suppress-transaction TransactionScope:

using ( new TransactionScope (TransactionScopeOption.Suppress ) )

{

// work with second connection (open etc.)

}

|||

Hi Vadim,

This solution worked great for me! Thanks

Nick

Multiple Connections in Managed Trigger

Hi All,

I am trying to open multiple connections in a Managed Trigger but encoutering an error as :

System.Data.SqlClient.SqlException: Transaction context in use by another session.

Below is the sample code:

public partial class Triggers
{

[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}

Any help please ?

Thanks...

Hi Nagul!

Most probably, your trigger runs in a transaction. And I think, your second connection is a loopback connection to the same server (which creates a new session). Currently, two sessions cannot share one transaction. You can avoid the problem by placing the second connection in suppress-transaction TransactionScope:

using ( new TransactionScope (TransactionScopeOption.Suppress ) )

{

// work with second connection (open etc.)

}

|||

Hi Vadim,

This solution worked great for me! Thanks

Nick

Multiple connections in a CLR trigger

Hello

Is there nay way that it is possible to connect to another database within a trigger…

My scenario is that an update in a table in database X triggers several updates in a table in Database Y. The databases are on the same SQL Server

Thanks for any ideas, solutions or hints you can give me..!!!

Hi

I developed one solution like that Try Referencing server Y in server X wthi sp_addlinkedserver and then directly try to make the update like this

update servery.database.dbo.tabley set yfield=xfield

from serverx.database.dbo.tablex

|||

I tried the suggested function but i didn’t help and I still didn’t figure out how to make it work

Cheers

|||Did you try to just instatiate a new sqlconnection object using a normal connection string ? Make sure that you will need to grant a special trust level (external) to the assembly to make it work.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I did try to instantiate a new sqlconnection object. But I sadly I don't know about the trust level which you are talking about... Could you give me further information have I could grant the trust level to the assembly? It would be highly appreciated

Thanks

|||This would be a quick sample:

// Create Table Table1

//(

// SomeInt INT

//)

//INSERT INTO Table1 VALUES (1)

//EXEC sp_configure 'clr enabled' , '1'

//ALTER DATABASE Northwind SET TRUSTWORTHY ON

//RECONFIGURE

// Enter existing table or view for the target and uncomment the attribute line

[Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target = "Table1", Event = "FOR INSERT")]

public static void Trigger1()

{

SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select 1";

conn.Open();

cmd.ExecuteNonQuery();

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti