Triggers?
-
Very new to SQL. I think what I would like to do may be a trigger so just checking.
I've got a table called dates which has all workable dates during the academic year and the period they fall under. I've also got a table for contracts which as the startdate of the contract. What I would like is when a date is entered in the field startdate it calls a trigger or something that looks at the date and compares it with the dates table and inserts the period from dates to nextperiod field in contracts.
I've created a trigger but does not seem to work
CREATE TRIGGER t_period
ON Contracts
FOR INSERT, update
AS
Declare @Nextperiod float
select
@nextperiod = period
from dates inner join contracts
on dates.dateses = contracts.startdate
where contracts.startdate = dates.dateses
Can anyone help
-
After comparing what do you want to do
That actionis missing in your trigger
My Blog:
http://dineshasanka.spaces.live.com/ -
??? what do you mean
-
Can you show us some sample data from the 2 tables?
I'm not 100% sure it's possible with you current design.
-
Sample from dates
Period Dateses Worked 1 01/08/04 True 1 02/08/04 True 1 03/08/04 True 1 04/08/04 True 1 05/08/04 True 1 06/08/04 True Contracts
ContractID Pay_No startdate enddate NextPeriod 76 0349144 01/08/04 31/01/05 81 0028712 01/08/04 31/01/05 82 0349144 01/10/04 31/10/04 83 4000125 01/02/05 30/06/05 84 0028712 01/08/04 30/11/04 85 0028712 01/09/04 05/06/31 102 0028712 01/09/04 31/03/05 104 0028712 01/09/04 30/05/05 -
What do you want the trigger to put in the next period column (date, id)??
-
period from dates
-
Do you want the period of the next date, or just the next period?
PS that's why you should also supply the expected result of the query when asking a question
. -
contracts.startdate = 01-08-04 then nextperiod = 1 form dates.period
-
The update in the trigger would look something like this :
CREATE TRIGGER t_period
ON Contracts
FOR INSERT, update
AS
SET NOCOUNT ON
IF UPDATE (StartDate)
begin
UPDATE C SET C.NextPeriod = dtNextPeriod.NextPeriod FROM Contracts C inner join
(
Select dtNextDates.ContractID, dtNextDates.StartDate, dtNextDates.Dateses, D.Period as NextPeriod from
(
Select
C.ContractID, C.StartDate, MIN(D.Dateses) as Dateses
from dbo.Contracts C inner join dbo.Dates D on C.StartDate < D.Dateses inner join Inserted I on C.ContractId = I.ContractId
group by C.ContractID, C.StartDate
) dtNextDates
inner join dbo.Dates D on dtNextDates.Dateses = D.Dateses) dtNextPeriod
on C.ContractID = dtNextPeriod.ContractID
end
run this on another database to see if it works for you :
Create table dates
(
period int not null,
Dateses smalldatetime not null,
worked bit not null default 1
)
Insert into dates (period, Dateses) values (1, '2004/08/01')
Insert into dates (period, Dateses) values (1, '2004/08/02')
Insert into dates (period, Dateses) values (1, '2004/08/03')
Insert into dates (period, Dateses) values (1, '2004/08/04')
Insert into dates (period, Dateses) values (2, '2004/08/05')
Insert into dates (period, Dateses) values (2, '2004/08/06')
Select * from Dates
GO
Create table contracts
(
ContractID int not null,
Pay_No char(7) not null,
startdate smalldatetime not null,
enddate smalldatetime not null,
NextPeriod int null
)
GO
Insert into dbo.Contracts (ContractID, Pay_No, startdate, enddate) values (76, '0349144', '2004/08/01', '2005/01/31')
Insert into dbo.Contracts (ContractID, Pay_No, startdate, enddate) values (85, '0028712', '2004/08/05', '2005/02/05')
Select * from Contracts
UPDATE C SET C.NextPeriod = dtNextPeriod.NextPeriod FROM Contracts C inner join
(
Select dtNextDates.ContractID, dtNextDates.StartDate, dtNextDates.Dateses, D.Period as NextPeriod from
(
Select
C.ContractID, C.StartDate, MIN(D.Dateses) as Dateses
from dbo.Contracts C inner join dbo.Dates D on C.StartDate < D.Dateses
group by C.ContractID, C.StartDate
) dtNextDates
inner join dbo.Dates D on dtNextDates.Dateses = D.Dateses) dtNextPeriod
on C.ContractID = dtNextPeriod.ContractID
Select * from Contracts
--drop table contracts
--drop table dates
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply