Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need Help with Triggers` Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 1:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 7:30 AM
Points: 26, 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
Post #1533807
Posted Wednesday, January 22, 2014 1:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
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)
Post #1533812
Posted Wednesday, January 22, 2014 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 7:30 AM
Points: 26, 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]
Post #1533816
Posted Wednesday, January 22, 2014 2:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 7:30 AM
Points: 26, 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
Post #1533847
Posted Wednesday, January 22, 2014 2:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
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)
Post #1533849
Posted Wednesday, January 22, 2014 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
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)
Post #1533851
Posted Wednesday, January 22, 2014 3:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 7:30 AM
Points: 26, 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

Post #1533854
Posted Wednesday, January 22, 2014 3:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 7:30 AM
Points: 26, 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
Post #1533856
Posted Wednesday, January 22, 2014 3:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
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)
Post #1533860
Posted Wednesday, January 22, 2014 3:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 7:30 AM
Points: 26, 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...
Post #1533861
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse