Request help writing a simple trigger!

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 2005 Forums

Transact-SQL (2005)

Request help writing a simple trigger!

Author

Topic

keuler
Starting Member

10 Posts

Posted - 2008-02-26 : 22:35:50

Experts: Please assist with coding a trigger for a SQL Server 2005 .NET application.

Here's the scenario:

Suppose there are tables MEMBERS, ACTIVITY, and HEADCOUNT that look like this:

MEMBER
member_id (int)
member_name (varchar(50))
...etc

ACTIVITY
activity_id (int)
activity_name (varchar(50))
...etc

HEADCOUNT
headcount_id (int)
member_id (int)
activity_id (int)
...etc

Suppose also that the ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.

OBJECTIVE: When a new member record is added to MEMBER, say member_id 10, insert one record in the HEADCOUNT table for EACH activity in ACTIVITY for that member. Thus, if member #10 is added to MEMBER, then the trigger (or some other mechanism) would add the following records to HEADCOUNT (which, say, already has 30 records):

headcount_id member_id activity_id
31 10 1
32 10 2
33 10 3

I've been advised that a trigger should do the trick for this, but as I'm totally new to SQL, I'll need some help. I'm guessing some iterating SQL command language might be required, but as I'm new to SQL, I don't know how to proceed.

Note that I'm building an ASP.NET application based on VB, and so records will be added to MEMBER through a tableadapter INSERT command. (Though I suspect this has no bearing on trigger behavior.)

Much obliged for your assistance.

-Kurt Euler
San Jose, CA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 23:05:35

[code]CREATE TRIGGER InsertHeadCount
ON MEMBER
AFTER INSERT
AS
INSERT INTO HEADCOUNT(member_id,activity_id)
SELECT i.member_id,a.activity_id
FROM INSERTED i
CROSS JOIN ACTIVITY a
GO[/code]

I'm assuming headcount_id is identity field. if not change insert as

[code]INSERT INTO HEADCOUNT(headcount_id,member_id,activity_id)
SELECT (SELECT CASE WHEN MAX(headcount_id) IS NULL THEN 1
ELSE MAX(headcount_id)
END
FROM HEADCOUNT),
i.member_id,a.activity_id
FROM INSERTED i
CROSS JOIN ACTIVITY a[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-26 : 23:41:26

This doesn't seem like a good use for a trigger. You would be better of putting this in the stored procedure that you are using to inset the new row into MEMBER.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 00:04:13

If you want do by procedure:-

CREATE PROCEDURE InsertData
@member_name varchar(50)
AS
INSERT INTO MEMBER (member_name,...)
VALUES (@member_name,....)

INSERT INTO HEADCOUNT (member_id,activity_id)
SELECT SCOPE_IDENTITY(),
       activity_id
FROM ACTIVITY
GO

keuler
Starting Member

10 Posts

Posted - 2008-02-27 : 22:38:00

Visakh and Michael- Many thanks for your code. I'll give these a try. Could I ask you to address a few more questions:

1) Michael, I'm curious as to why you think this wouldn't be a good use for a trigger. (I'm sure there's a reason, but as a newbie I don't know.)

2) Visakh (or Michael), in your procedure and trigger examples, supposing the HEADCOUNT table also included three bit fields (bit1, bit2, bit3) after the activity_id field. How would you adjust your code so that for each record inserted into HEADCOUNT, those fields are given the values TRUE, FALSE, and FALSE, respectively.

3) If I were to go with the stored procedure design, where would I call that procedure from? Currently, the records are inserted into the MEMBER table via a TableAdapter. The INSERT section for this is shown below (in the code below the MEMBER table is actually named person.) Do I call the procedure somewhere after the list of INSERT parameters?

Thanks again!

-Kurt
(I've ordered a SQL Server book, which will hopefully keep me from asking too many questions in future.)

              <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="false">
                    <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" />
                    </Parameters>
                  </DbCommand>
                </InsertCommand>
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">

.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 00:03:20

quote:

Originally posted by keuler

Visakh and Michael- Many thanks for your code. I'll give these a try. Could I ask you to address a few more questions:

1) Michael, I'm curious as to why you think this wouldn't be a good use for a trigger. (I'm sure there's a reason, but as a newbie I don't know.)

2) Visakh (or Michael), in your procedure and trigger examples, supposing the HEADCOUNT table also included three bit fields (bit1, bit2, bit3) after the activity_id field. How would you adjust your code so that for each record inserted into HEADCOUNT, those fields are given the values TRUE, FALSE, and FALSE, respectively.

CREATE PROCEDURE InsertData
@member_name varchar(50)
AS
INSERT INTO MEMBER (member_name,...)
VALUES (@member_name,....)

INSERT INTO HEADCOUNT (member_id,activity_id,bit1,bit2,bit3)
SELECT SCOPE_IDENTITY(),
       activity_id,
        1,
        0,
        0
FROM ACTIVITY
GO

3) If I were to go with the stored procedure design, where would I call that procedure from? Currently, the records are inserted into the MEMBER table via a TableAdapter. The INSERT section for this is shown below (in the code below the MEMBER table is actually named person.) Do I call the procedure somewhere after the list of INSERT parameters?

Not too sure how to do this from table adapter. I'm basically a db developer. Refer this links & see if these helps:-

http://msdn2.microsoft.com/en-us/library/37hwc7kt.aspx
http://forums.asp.net/p/1011278/1350537.aspx

Thanks again!

-Kurt
(I've ordered a SQL Server book, which will hopefully keep me from asking too many questions in future.)

              <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="false">
                    <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" />
                    </Parameters>
                  </DbCommand>
                </InsertCommand>
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">

.......

keuler
Starting Member

10 Posts

Posted - 2008-02-29 : 01:18:28

Thanks Visakh. How would the TRIGGER be written to include the three bit fields. Would you just add the 1,0,0 to the end of the insert line thus:

CREATE TRIGGER InsertHeadCount
ON MEMBER
AFTER INSERT 
AS
INSERT INTO HEADCOUNT(member_id,activity_id,1,0,0)
SELECT i.member_id,a.activity_id
FROM INSERTED i
CROSS JOIN ACTIVITY a
GO

Thanks!

-Kurt

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-02-29 : 06:15:21

[code]CREATE TRIGGER InsertHeadCount
ON MEMBER
AFTER INSERT
AS
INSERT INTO HEADCOUNT(member_id,activity_id,BitField1,BitField2,BitField3)
SELECT i.member_id,a.activity_id,1,0,0
FROM INSERTED i
CROSS JOIN ACTIVITY a
GO[/code]

--
Lumbago
"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4"

keuler
Starting Member

10 Posts

Posted - 2008-02-29 : 11:55:17

Turns out the trigger works fine if I remove the "GO" at the end.

Thx. for the help.

-Kurt Euler

Lumbago-

I just attempted to create a trigger, just below, according to your model you supplied earlier. When I attempted to save it, I get the error message "Incorrect syntax near 'GO'". I can't see what I'm doing wrong tho. Can you assist? Thanks. (Note: in the trigger I'm writing, table "people" is the correct table name. In earlier discussion we used "member". Note to that I supply hear the actual names of the bit fields: no_answer,not_coming, and coming)

CREATE TRIGGER InsertHeadCount
ON dbo.person
AFTER INSERT
AS
INSERT INTO
HEADCOUNT(person_id,activity_id,no_answer,not_coming,coming)
SELECT i.member_id,a.activity_id,1,0,0
FROM INSERTED i
CROSS JOIN ACTIVITY a
GO

Regards,

Kurt
SJ, California

Thanks!

quote:
Originally posted by Lumbago

CREATE TRIGGER InsertHeadCount
ON MEMBER
AFTER INSERT
AS
INSERT INTO HEADCOUNT(member_id,activity_id,BitField1,BitField2,BitField3)
SELECT i.member_id,a.activity_id,1,0,0
FROM INSERTED i
CROSS JOIN ACTIVITY a
GO

--
Lumbago
"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources