Insert Trigger - SQL Server Forums

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)

Insert Trigger

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 11:56:44

Hi there,

After some head scratching and much research and trying I am unable to figure out how to do the following Insert Trigger. Maybe it shows a flaw in my database design?

I have a user front end where a user can assign a certain job to be done and assign it to one and only one shift and one or more equipments to do this job on.
When user clicks on Assign button it will insert rows into Jobs table via an insert stored procedure. Jobs table has following fields : ShiftID, and a JobEquipmentID since Job can be done on one or more equipment. This field, JobEquipmentID, will refer to a child table JobEquipment. What is the best approach to update this secondary table? I need to update JobEquipmentID but I do not have it until I update JobEquipment table? CRAZY!!! HELP

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-05 : 12:33:15

I fail to understand why scheduling a piece of equipment for a job requires you to update the equipment table.

Post the DDL for you tables so we can see the relationships.

e4 d5 xd5 Nf6

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 12:49:25

Sorry I am not updating the equipment table JobEquipment is the middle table for Jobs and Equipment. I am updating the JobEquipment table. Sorry it is a lot of code.!

CREATE TABLE [dbo].[Jobs](
[JobID] [int] IDENTITY(1,1) NOT NULL,
[TagNumber] [char](6) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL,
[ShiftID] [int] NOT NULL,
[JobOperatorID] [int] NULL,
[JobEquipmentID] [int] NOT NULL,
[JobComments] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[UserDate] [datetime] NOT NULL CONSTRAINT [DF_Jobs_UserDate] DEFAULT (getdate()),
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[JobEquipments](
[JobEquipmentID] [int] IDENTITY(1,1) NOT NULL,
[JobID] [int] NOT NULL,
[EquipmentID] [int] NOT NULL,
[JobEquipmentComments] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[UserDate] [datetime] NOT NULL CONSTRAINT [DF_JobEquipments_UserDate] DEFAULT (getdate()),
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Equipments](
[EquipmentID] [int] IDENTITY(1,1) NOT NULL,
[BranchID] [int] NOT NULL,
[EquipmentDescr] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EquipmentComments] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDate] [datetime] NOT NULL CONSTRAINT [DF_Equipments_UserDate] DEFAULT (getdate()),
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 12:55:15

Ok I think I see my problem! Job does not need JobEquipmentID nor JobOperatorID as FKeys!!!!!

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-06-05 : 13:14:40

If I am reading this correctly, not always a good assumption, it looks like you have a one-to-many relation between Jobs and JobsEquipment. The problem, as I see it, is that the Parent table (Jobs) is trying to use one key (JobEquipmentID) to refer to the many pieces (rows) of equipment in the child table.

I'd suggest that you don't need or want the JobEquipmentID column in the Jobs table. Just let the Child table (JobsEquipment) have a foreign key relationship with the Jobs table using the JobsID column.

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-06-05 : 13:28:11

Um...What you said...

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 13:40:57

:O) yes sir, you are correct! Now a new problem arises the insert into jobs store procedure receives the parameters:
@ShiftID AS INT,
@strEquipmentID AS VARCHAR(MAX),
@User AS VARCHAR(20)
But now how do I insert the comma delimited @strEquipmentID INTO JobEquipment. I was thinking a insert trigger but how? Should I also have an INSERT INTO JobEquipment in the same store procedure, that sounds like a hack job!?!?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 13:48:24

quote:
Originally posted by yosiasz

:O) yes sir, you are correct! Now a new problem arises the insert into jobs store procedure receives the parameters:
@ShiftID AS INT,
@strEquipmentID AS VARCHAR(MAX),
@User AS VARCHAR(20)
But now how do I insert the comma delimited @strEquipmentID INTO JobEquipment. I was thinking a insert trigger but how? Should I also have an INSERT INTO JobEquipment in the same store procedure, that sounds like a hack job!?!?

You could use a UDF to parse the comma delimited string and gets values onto a table. Then insert the values from this table onto your secondary table

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 14:11:51

Like this? It inserts into Jobs but not into JobEquipment. Is inserted table a virtual table provided by SQL ?
INSERT INTO dbo.VJobs
(TagNumber,
ShiftID,
UserName)
SELECT tag.strJoinerField,
@ShiftID ,
@User
FROM dbo.str_CreateTableFromList(@Tags, DEFAULT) tag

INSERT INTO dbo.JobEquipments
(JobID,
EquipmentID)
SELECT vj.JobID,
eqp.intJoinerField
FROM VJobs vj
INNER JOIN inserted i
ON vj.JobID = i.JobID
CROSS JOIN dbo.str_CreateTableFromList_int(@EquipmentID, DEFAULT) eqp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-05 : 14:14:38

quote:
Originally posted by yosiasz

Is inserted table a virtual table provided by SQL ?

They are tables that exist in triggers. We've got inserted and deleted, just depends on what the trigger is doing. For inserts, the inserted trigger table contains the rows that were inserted. For updates, the inserted table contains the after image of the rows, and the deleted table contains the before image of the rows. For deletes, the deleted table contains the rows that were deleted.

Check SQL Server Books Online for more details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 14:24:06

I humbly bow to you Almighty Goddess..SQL2005 rocks wow!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 14:44:17

ok I am doing as the documentation says but when adding a trigger just to see what is in the inserted table it is blank? why could that be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-05 : 15:11:41

Please post your trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-06 : 12:33:29

HEre is this trigger
--insert into job
--insert into jobequipment
CREATE TRIGGER Jobs_InsertJobsEquipment_instrg ON dbo.Jobs
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.JobEquipments
(JobID)
SELECT vj.JobID
FROM VJobs vj
INNER JOIN inserted i

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-06 : 12:42:29

That trigger wouldn't compile, so could you post your actual trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-06 : 14:01:39

sorry Tara

Forgot to add the END
CREATE TRIGGER Jobs_InsertJobsEquipment_instrg ON dbo.Jobs
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.VJobEquipments
(JobID)
SELECT vj.JobID
FROM VJobs vj
INNER JOIN inserted i
ON vj.JobID = i.JobID
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-06 : 14:03:35

What is VJobs? Is it a view? If it is, please post it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources