Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help with Triggers`


Need Help with Triggers`

Author
Message
Dieselbf2
Dieselbf2
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 33
SELECT [WorksheetID]
,[DateTimeCreated]
,[CreatedByUserID]
,[CreatedByImpersonatingUserID]
,[ProposalUniqueID]
,[ScenarioNumber]
,[BillingBranchID]
,[SiteSurveyBranchID]
,[DateTimeAcceptedBySellingBranch]
,[AcceptedBySellingBranchUserID]
,[AcceptedBySellingBranchImpersonatingUserID]
,[DateTimeAcceptedByInstallingBranch]
,[AcceptedByInstallingBranchUserID]
,[AcceptedByInstallingBranchImpersonatingUserID]
FROM [101WareTest].[dbo].[ProposalProfitSplitWorksheet]

SELECT TOP 1000 [UniqueID], [PropID], [Rev], [FRID], [NatFRID], [us], [ActiveRev], [COID], [BillToCOID], [CPID], [LeadID],[ProjID], [LgID], [SalesEng]
, [title], [status], [created], [resolution], [resolutiondt], [resolvedby], [PMappDt], [PMappLgID], [CustAppDt], [CustAppLgID], [CustAppCPID], [FinAppDt], [FinAppLgID], [SalesTax], [CustPO], [PricingMethod], [TaxMethod], [PaymentTerms], [TermsConds], [PCClevel], [ProjCloseDt], [ProjCompDt], [SpecConds], [ProjContact], [MasterPermit], [ElectricPermit], [folio], [legal], [OneTime], [AdjRsn], [dirtyPrice], [recentPrice], [recentPandM], [recentLabor], [recentOther], [ptDetail], [isTemplate], [SysList], [rollups], [prefs], [template], [message], [POnotes]
FROM [101WareTest].[dbo].[proposals]

SELECT [LgID], [FRID], [BRID], [firstname], [lastname], [jobtitle], [email], [office], [cellphone], [hidecell], [noDirectory], [username]
, [password], [admin], [owner], [lockoutct1], [lockoutct2], [remlogin], [inactive], [appLimit], [svcLimit], [PropPrefs], [coRollups], [propMessage], [dailyemail], [FwdEmail], [mustChgPW], [BBtoken], [mailBox], [statusDt], [status], [helpdesk], [birthmonth], [birthday], [HomePhone], [HomeAdd], [PayPeriod], [ComDeal], [OvrDeal], [BoxDeal], [BoxOvr], [SvcDeal], [SvcOvr], [Exempt], [Boss], [EmpNo], [Rate], [HWM], [HireDate], [LastRev], [NextRev], [lastView], [lastFilterType], [lastFilterRec], [ipaddress]
FROM [101WareTest].[dbo].[logins]

These are the 3 Tables above. I need to check that the FRID's on the proposals & logins Tables are equal before letting the LgID inserted into the column CreatedByUserID.. I am thinking and If Exist but I am not sure.

Thanks
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
Hi and welcome to the forums. It is very difficult to figure out what you are trying to do with nothing more than a couple of queries. Can you post the ddl for the tables and a few rows of sample data? This would make it a million times easier to help. I am pretty sure from what you are explaining that you can do this a check constraint instead of a trigger.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Dieselbf2
Dieselbf2
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 33
Thank you! I think this is what you are asking for.. My Boss wants a Trigger and the reason is maybe to just get me use to writing more logic instead og updat, Delete, and insert along with basic SQL quires as well. Any help would be greatly appreciated

Thank you

USE [101WareTest]
GO

/****** Object: Table [dbo].[ProposalProfitSplitWorksheet] Script Date: 1/22/2014 3:24:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProposalProfitSplitWorksheet](
[WorksheetID] [int] IDENTITY(1,1) NOT NULL,
[DateTimeCreated] [datetime] NOT NULL,
[CreatedByUserID] [int] NOT NULL,
[CreatedByImpersonatingUserID] [int] NULL,
[ProposalUniqueID] [int] NOT NULL,
[ScenarioNumber] [int] NOT NULL,
[BillingBranchID] [int] NOT NULL,
[SiteSurveyBranchID] [int] NOT NULL,
[DateTimeAcceptedBySellingBranch] [datetime] NULL,
[AcceptedBySellingBranchUserID] [int] NULL,
[AcceptedBySellingBranchImpersonatingUserID] [int] NULL,
[DateTimeAcceptedByInstallingBranch] [datetime] NULL,
[AcceptedByInstallingBranchUserID] [int] NULL,
[AcceptedByInstallingBranchImpersonatingUserID] [int] NULL,
CONSTRAINT [PK_ProposalProfitSplitWorksheet] PRIMARY KEY CLUSTERED
(
[WorksheetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_db_branches] FOREIGN KEY([BillingBranchID])
REFERENCES [dbo].[db_branches] ([BRID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_db_branches]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID] FOREIGN KEY([AcceptedByInstallingBranchImpersonatingUserID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID] FOREIGN KEY([AcceptedByInstallingBranchUserID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID] FOREIGN KEY([AcceptedBySellingBranchImpersonatingUserID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID] FOREIGN KEY([AcceptedBySellingBranchUserID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID] FOREIGN KEY([CreatedByImpersonatingUserID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID] FOREIGN KEY([CreatedByUserID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_proposals] FOREIGN KEY([ProposalUniqueID])
REFERENCES [dbo].[proposals] ([UniqueID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_proposals]
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [CK_ProposalProfitSplitWorksheet_ScenarioNumber] CHECK (([ScenarioNumber]=(2) OR [ScenarioNumber]=(1)))
GO

ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [CK_ProposalProfitSplitWorksheet_ScenarioNumber]
Dieselbf2
Dieselbf2
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 33
Here is what I have so far. I am just trying to get the trigger to check the 2 frid that are on different tables before inserting the LgID into the [dbo].[ProposalProfitSplitWorksheet table:

Am I on the right track


CREATE TRIGGER [UProposalProfitSplitWorksheet]
ON [dbo].[ProposalProfitSplitWorksheet]
FOR INSERT, UPDATE
AS

Declare @LFRID int
Declare @PFRID int

Select @LFRID = (Select L.FRID From Inserted As I Join logins As L On I.lgID = L.LgID)
Select @PFRID = (Select P.FRID From Inserted As I Join proposals As L On I.LgID = P.LgID)

Thanks
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
Dieselbf2 (1/22/2014)
Thank you! I think this is what you are asking for.. My Boss wants a Trigger and the reason is maybe to just get me use to writing more logic instead og updat, Delete, and insert along with basic SQL quires as well. Any help would be greatly appreciated

Thank you



That is certainly the ddl. Unfortunately that is for only one table. The other challenge is there are lots of errors when I try to load this on my local instance.


Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_db_branches' references invalid table 'dbo.db_branches'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_db_branches' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_proposals' references invalid table 'dbo.proposals'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_proposals' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.



Obviously you have a little work on the ddl before we can get much going here. Why does your boss ask for this to be done in a trigger? What they are asking for is referential integrity and triggers are not a good choice for that. One issue is you will likely have to write at least 2 triggers for this. An update and an insert. The reason triggers are not good at enforcing RI is because they can be disabled. Let's say I want to force a row in that does not meet the criteria. I simply disable the trigger, insert my data and then enable the trigger.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
Dieselbf2 (1/22/2014)
Here is what I have so far. I am just trying to get the trigger to check the 2 frid that are on different tables before inserting the LgID into the [dbo].[ProposalProfitSplitWorksheet table:

Am I on the right track


CREATE TRIGGER [UProposalProfitSplitWorksheet]
ON [dbo].[ProposalProfitSplitWorksheet]
FOR INSERT, UPDATE
AS

Declare @LFRID int
Declare @PFRID int

Select @LFRID = (Select L.FRID From Inserted As I Join logins As L On I.lgID = L.LgID)
Select @PFRID = (Select P.FRID From Inserted As I Join proposals As L On I.LgID = P.LgID)

Thanks


This might be somewhat ok with one MAJOR exception. It cannot handle multiple row inserts or updates.

Consider what value would be in @LFRID if someone ran the following update statement.


UPDATE ProposalProfitSplitWorksheet
set FRID = 9
where LgID in (1,2,3,4,5,6)



I will reiterate that trying to use a trigger for RI is the wrong methodology. My guess is that you want to create a function that you can use in a check constraint on the FRID column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Dieselbf2
Dieselbf2
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 33
Sean Lange (1/22/2014)
Dieselbf2 (1/22/2014)
Here is what I have so far. I am just trying to get the trigger to check the 2 frid that are on different tables before inserting the LgID into the [dbo].[ProposalProfitSplitWorksheet table:

Am I on the right track


CREATE TRIGGER [UProposalProfitSplitWorksheet]
ON [dbo].[ProposalProfitSplitWorksheet]
FOR INSERT, UPDATE
AS

Declare @LFRID int
Declare @PFRID int

Select @LFRID = (Select L.FRID From Inserted As I Join logins As L On I.lgID = L.LgID)
Select @PFRID = (Select P.FRID From Inserted As I Join proposals As L On I.LgID = P.LgID)

Thanks


This might be somewhat ok with one MAJOR exception. It cannot handle multiple row inserts or updates.

Consider what value would be in @LFRID if someone ran the following update statement.


UPDATE ProposalProfitSplitWorksheet
set FRID = 9
where LgID in (1,2,3,4,5,6)



I will reiterate that trying to use a trigger for RI is the wrong methodology. My guess is that you want to create a function that you can use in a check constraint on the FRID column.


If I did it the way you are talking about, then every time my company got a new employee I would have to add the LgID and we have 39 FRID's.

Any help is appreciated
Dieselbf2
Dieselbf2
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 33
Sean Lange (1/22/2014)
Dieselbf2 (1/22/2014)
Here is what I have so far. I am just trying to get the trigger to check the 2 frid that are on different tables before inserting the LgID into the [dbo].[ProposalProfitSplitWorksheet table:

Am I on the right track


CREATE TRIGGER [UProposalProfitSplitWorksheet]
ON [dbo].[ProposalProfitSplitWorksheet]
FOR INSERT, UPDATE
AS

Declare @LFRID int
Declare @PFRID int

Select @LFRID = (Select L.FRID From Inserted As I Join logins As L On I.lgID = L.LgID)
Select @PFRID = (Select P.FRID From Inserted As I Join proposals As L On I.LgID = P.LgID)

Thanks


This might be somewhat ok with one MAJOR exception. It cannot handle multiple row inserts or updates.

Consider what value would be in @LFRID if someone ran the following update statement.


UPDATE ProposalProfitSplitWorksheet
set FRID = 9
where LgID in (1,2,3,4,5,6)



I will reiterate that trying to use a trigger for RI is the wrong methodology. My guess is that you want to create a function that you can use in a check constraint on the FRID column.




Here are the other 2 tables


USE [101WareTest]
GO

/****** Object: Table [dbo].[proposals] Script Date: 1/22/2014 4:57:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[proposals](
[UniqueID] [int] IDENTITY(1000,1) NOT NULL,
[PropID] [int] NOT NULL,
[Rev] [int] NOT NULL,
[FRID] [int] NOT NULL,
[NatFRID] [int] NOT NULL,
[us] [bit] NOT NULL,
[ActiveRev] [bit] NOT NULL,
[COID] [int] NOT NULL,
[BillToCOID] [int] NOT NULL,
[CPID] [int] NOT NULL,
[LeadID] [int] NOT NULL,
[ProjID] [int] NOT NULL,
[LgID] [int] NOT NULL,
[SalesEng] [int] NOT NULL,
[title] [nvarchar](100) NOT NULL,
[status] [int] NOT NULL,
[created] [datetime] NOT NULL,
[resolution] [int] NOT NULL,
[resolutiondt] [date] NULL,
[resolvedby] [int] NOT NULL,
[PMappDt] [date] NULL,
[PMappLgID] [int] NOT NULL,
[CustAppDt] [date] NULL,
[CustAppLgID] [int] NOT NULL,
[CustAppCPID] [int] NOT NULL,
[FinAppDt] [datetime] NULL,
[FinAppLgID] [int] NOT NULL,
[SalesTax] [int] NOT NULL,
[CustPO] [nvarchar](50) NULL,
[PricingMethod] [int] NOT NULL,
[TaxMethod] [tinyint] NOT NULL,
[PaymentTerms] [int] NOT NULL,
[TermsConds] [int] NOT NULL,
[PCClevel] [int] NOT NULL,
[ProjCloseDt] [date] NULL,
[ProjCompDt] [date] NULL,
[SpecConds] [int] NOT NULL,
[ProjContact] [int] NOT NULL,
[MasterPermit] [nvarchar](50) NULL,
[ElectricPermit] [nvarchar](50) NULL,
[folio] [nvarchar](100) NULL,
[legal] [nvarchar](1000) NULL,
[OneTime] [decimal](10, 2) NULL,
[AdjRsn] [int] NOT NULL,
[dirtyPrice] [bit] NOT NULL,
[recentPrice] [decimal](10, 2) NULL,
[recentPandM] [decimal](10, 2) NULL,
[recentLabor] [decimal](10, 2) NULL,
[recentOther] [decimal](10, 2) NULL,
[ptDetail] [nvarchar](1000) NULL,
[isTemplate] [bit] NOT NULL,
[SysList] [nvarchar](100) NULL,
[rollups] [char](14) NOT NULL,
[prefs] [nvarchar](50) NULL,
[template] [int] NOT NULL,
[message] [nvarchar](100) NULL,
[POnotes] [nvarchar](1000) NULL,
CONSTRAINT [PK_proposals] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_proposals] UNIQUE NONCLUSTERED
(
[PropID] ASC,
[Rev] ASC,
[FRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_NatFRID] DEFAULT ((0)) FOR [NatFRID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_BillToCOID] DEFAULT ((0)) FOR [BillToCOID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_ProjID] DEFAULT ((0)) FOR [ProjID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_SalesEng] DEFAULT ((0)) FOR [SalesEng]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_status] DEFAULT ((0)) FOR [status]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_created] DEFAULT (getdate()) FOR [created]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_resolution] DEFAULT ((0)) FOR [resolution]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_resolvedby] DEFAULT ((0)) FOR [resolvedby]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_PMappLgID] DEFAULT ((0)) FOR [PMappLgID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_CustAppLgID] DEFAULT ((0)) FOR [CustAppLgID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_CustAppCPID] DEFAULT ((0)) FOR [CustAppCPID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_FinAppLgID] DEFAULT ((0)) FOR [FinAppLgID]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_SalesTax] DEFAULT ((0)) FOR [SalesTax]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_PaymentTerms] DEFAULT ((0)) FOR [PaymentTerms]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_TermsConds] DEFAULT ((0)) FOR [TermsConds]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_PCClevel] DEFAULT ((0)) FOR [PCClevel]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_SpecConditions] DEFAULT ((0)) FOR [SpecConds]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_ProjContact] DEFAULT ((0)) FOR [ProjContact]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_AdjRsn] DEFAULT ((-1)) FOR [AdjRsn]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_dirtyPrice] DEFAULT ((1)) FOR [dirtyPrice]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_isTemplate] DEFAULT ((0)) FOR [isTemplate]
GO

ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_template] DEFAULT ((0)) FOR [template]
GO

ALTER TABLE [dbo].[proposals] WITH CHECK ADD CONSTRAINT [FK_proposals_companies] FOREIGN KEY([COID])
REFERENCES [dbo].[companies] ([COID])
GO

ALTER TABLE [dbo].[proposals] CHECK CONSTRAINT [FK_proposals_companies]
GO

ALTER TABLE [dbo].[proposals] WITH CHECK ADD CONSTRAINT [FK_proposals_logins] FOREIGN KEY([LgID])
REFERENCES [dbo].[logins] ([LgID])
GO

ALTER TABLE [dbo].[proposals] CHECK CONSTRAINT [FK_proposals_logins]
GO


USE [101WareTest]
GO

/****** Object: Table [dbo].[logins] Script Date: 1/22/2014 5:02:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[logins](
[LgID] [int] IDENTITY(1,1) NOT NULL,
[FRID] [int] NOT NULL,
[BRID] [int] NOT NULL,
[firstname] [nvarchar](60) NOT NULL,
[lastname] [nvarchar](60) NOT NULL,
[jobtitle] [nvarchar](50) NOT NULL,
[email] [nvarchar](128) NOT NULL,
[office] [nvarchar](50) NOT NULL,
[cellphone] [nvarchar](50) NOT NULL,
[hidecell] [bit] NOT NULL,
[noDirectory] [bit] NOT NULL,
[username] [nvarchar](128) NOT NULL,
[password] [nvarchar](128) NOT NULL,
[admin] [tinyint] NOT NULL,
[owner] [bit] NOT NULL,
[lockoutct1] [tinyint] NOT NULL,
[lockoutct2] [tinyint] NOT NULL,
[remlogin] [bit] NOT NULL,
[inactive] [bit] NOT NULL,
[appLimit] [int] NOT NULL,
[svcLimit] [int] NOT NULL,
[PropPrefs] [nvarchar](50) NULL,
[coRollups] [char](14) NULL,
[propMessage] [nvarchar](100) NULL,
[dailyemail] [nvarchar](128) NULL,
[FwdEmail] [int] NOT NULL,
[mustChgPW] [bit] NOT NULL,
[BBtoken] [nvarchar](50) NULL,
[mailBox] [bit] NOT NULL,
[statusDt] [datetime] NULL,
[status] [nvarchar](100) NULL,
[helpdesk] [bit] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[HomePhone] [nvarchar](50) NULL,
[HomeAdd] [nvarchar](300) NULL,
[PayPeriod] [tinyint] NOT NULL,
[ComDeal] [int] NOT NULL,
[OvrDeal] [int] NOT NULL,
[BoxDeal] [int] NOT NULL,
[BoxOvr] [int] NOT NULL,
[SvcDeal] [int] NOT NULL,
[SvcOvr] [int] NOT NULL,
[Exempt] [bit] NOT NULL,
[Boss] [int] NOT NULL,
[EmpNo] [nvarchar](50) NULL,
[Rate] [decimal](8, 2) NOT NULL,
[HWM] [tinyint] NOT NULL,
[HireDate] [datetime] NULL,
[LastRev] [datetime] NULL,
[NextRev] [datetime] NULL,
[lastView] [int] NOT NULL,
[lastFilterType] [tinyint] NOT NULL,
[lastFilterRec] [int] NOT NULL,
[ipaddress] [nvarchar](24) NULL,
CONSTRAINT [PK_logins] PRIMARY KEY CLUSTERED
(
[LgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_logins_username] UNIQUE NONCLUSTERED
(
[username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_BRID] DEFAULT ((0)) FOR [BRID]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lockout] DEFAULT ((0)) FOR [lockoutct1]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lockoutct2] DEFAULT ((0)) FOR [lockoutct2]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_remlogin] DEFAULT ((0)) FOR [remlogin]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_inactive] DEFAULT ((0)) FOR [inactive]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_FwdEmail] DEFAULT ((0)) FOR [FwdEmail]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_mustChgPW] DEFAULT ((1)) FOR [mustChgPW]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_helpdesk] DEFAULT ((0)) FOR [helpdesk]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_birthmonth] DEFAULT ((0)) FOR [birthmonth]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_birthday] DEFAULT ((0)) FOR [birthday]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_PayPeriod] DEFAULT ((0)) FOR [PayPeriod]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_ComDeal] DEFAULT ((0)) FOR [ComDeal]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_OvrDeal] DEFAULT ((0)) FOR [OvrDeal]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_BoxDeal] DEFAULT ((0)) FOR [BoxDeal]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_BoxOvr] DEFAULT ((0)) FOR [BoxOvr]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_SvcDeal] DEFAULT ((0)) FOR [SvcDeal]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_SvcOvr] DEFAULT ((0)) FOR [SvcOvr]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_Exempt] DEFAULT ((0)) FOR [Exempt]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_Super] DEFAULT ((0)) FOR [Boss]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_Rate] DEFAULT ((0)) FOR [Rate]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_HWM] DEFAULT ((0)) FOR [HWM]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lastView] DEFAULT ((0)) FOR [lastView]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lastFilterType] DEFAULT ((0)) FOR [lastFilterType]
GO

ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lastFilterRec] DEFAULT ((0)) FOR [lastFilterRec]
GO

ALTER TABLE [dbo].[logins] WITH CHECK ADD CONSTRAINT [FK_logins_db_branches] FOREIGN KEY([BRID])
REFERENCES [dbo].[db_branches] ([BRID])
GO

ALTER TABLE [dbo].[logins] CHECK CONSTRAINT [FK_logins_db_branches]
GO

Thank you
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
Dieselbf2 (1/22/2014)
Sean Lange (1/22/2014)
Dieselbf2 (1/22/2014)
Here is what I have so far. I am just trying to get the trigger to check the 2 frid that are on different tables before inserting the LgID into the [dbo].[ProposalProfitSplitWorksheet table:

Am I on the right track


CREATE TRIGGER [UProposalProfitSplitWorksheet]
ON [dbo].[ProposalProfitSplitWorksheet]
FOR INSERT, UPDATE
AS

Declare @LFRID int
Declare @PFRID int

Select @LFRID = (Select L.FRID From Inserted As I Join logins As L On I.lgID = L.LgID)
Select @PFRID = (Select P.FRID From Inserted As I Join proposals As L On I.LgID = P.LgID)

Thanks


This might be somewhat ok with one MAJOR exception. It cannot handle multiple row inserts or updates.

Consider what value would be in @LFRID if someone ran the following update statement.


UPDATE ProposalProfitSplitWorksheet
set FRID = 9
where LgID in (1,2,3,4,5,6)



I will reiterate that trying to use a trigger for RI is the wrong methodology. My guess is that you want to create a function that you can use in a check constraint on the FRID column.


If I did it the way you are talking about, then every time my company got a new employee I would have to add the LgID and we have 39 FRID's.

Any help is appreciated



No you missed my point. Your trigger that you are working will not handle multiple row inserts/updates. When writing triggers you need to be careful to build things set based so you can handle that.

When you have a variable equal to a column in INSERTED you can't handle that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Dieselbf2
Dieselbf2
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 33
No you missed my point. Your trigger that you are working will not handle multiple row inserts/updates. When writing triggers you need to be careful to build things set based so you can handle that.

When you have a variable equal to a column in INSERTED you can't handle that.

[/quote]


So what would I have to change to get this to work as a trigger...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search