Need help writing a trigger

Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.

Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.

Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.

I'd really appreciate some help here. Thanks in advance for all your help.

------------
Here's the script
------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContractStatus]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblCompany] (
 [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblContractStatus] (
 [StatusID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
 [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblDeals] (
 [DealID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [int] NOT NULL ,
 [DealDate] [smalldatetime] NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [CashAmount] [smallmoney] NOT NULL ,
 [StatusID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblPhase] (
 [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblProduction] (
 [TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
 [DealID] [int] NOT NULL ,
 [PhaseID] [tinyint] NOT NULL ,
 [TransactionTimeStamp] [smalldatetime] NOT NULL ,
 [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
 (
 [CompanyID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD
 CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED
 (
 [StatusID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD
 CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
 (
 [PhaseTypeID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD
 CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
 (
 [DealID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD
 CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
 (
 [PhaseID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
 CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
 (
 [TransactionID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID]
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
 (
 [CompanyID]
 ) REFERENCES [dbo].[tblCompany] (
 [CompanyID]
 ),
 CONSTRAINT [FK_tblDeals_tblContractStatus] FOREIGN KEY
 (
 [StatusID]
 ) REFERENCES [dbo].[tblContractStatus] (
 [StatusID]
 ),
 CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
 (
 [DealID]
 ) REFERENCES [dbo].[tblDeals] (
 [DealID]
 ),
 CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
 (
 [PhaseID]
 ) REFERENCES [dbo].[tblPhase] (
 [PhaseID]
 )
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Description', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'Status'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO

------------
And here's some data
------------

INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed')
INSERT INTO [tblContractStatus] ([Status])VALUES('Hold')
INSERT INTO [tblContractStatus] ([Status])VALUES('Collections')
INSERT INTO [tblContractStatus] ([Status])VALUES('Legal')
INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')

OK,
The trigger should be placed in the table TransactionTimeStamp.
The Trigger Fires on Insert

The SQL should look Like This

Declare
 @.phasesDone int,
 @.phasesNeeded int,
 @.dealid int,
 @.phaseid int,
 @.phasetypeid int
Select @.dealid = dealid, @.phaseid = phaseid from inserted
Select @.phasesdone = count(*) from tblProduction where dealid = @.dealid and phaseid = @.phaseid
Select @.phasesneeded = count(*) from tblphase INNER JOIN tbldeals on tblphase.phasetypeid = tbldeals.phasetypeid where tbldeals.id = @.dealid
If @.phasesdone = @.phasesneeded
begin
 update tbldeals set statusid = 1 where id = @.dealid
end

that shoul work i'm quite sure.
hope this helps you.|||Hi Misiu,

Thanks for the help. I'm getting an error. I think it has somehting to do with getting the PhaseID and the DealID from the "inserted". For some reason I've never been able to get this to work for me. Is there anything I need to do i.e. activate, some kind of setting or something -- so that I can get data from the inserted?|||What error do you get?
As I know you don't have to activate any setting.