September 21, 2006 at 12:49 pm
I run this query and
update dbo.[Manager_List] set Manager='Frances ' where HOME_JOBCOST_NBR ='010027907'
got error like..
String or binary data would be truncated.
The statement has been terminated.
I tried to update other table tblWinMasterDueList while the manager change in the manager list table.
CREATE trigger UpdateWinMasterDueList on dbo.[Manager_List]
for INSERT, UPDATE
as
begin
DECLARE @HOME_JOBCOST_NBR nchar(9)
DECLARE @Manager varchar(30)
select @HOME_JOBCOST_NBR =inser.HOME_JOBCOST_NBR , @Manager=inser.Manager From inserted inser
Update tblWinMasterDueList set Manager=@Manager where JobCostCenter=@HOME_JOBCOST_NBR
end
September 21, 2006 at 1:01 pm
#1
I would look at the table defs for tblWinMasterDueList, possibly some of the fields are different lengths then similar fields on ManagerList.
#2
The trigger is not built for mulitple inserts.
September 21, 2006 at 1:05 pm
Thanks.
September 21, 2006 at 1:07 pm
For multiple inserts/updates
CREATE trigger UpdateWinMasterDueList on dbo.[Manager_List]
for INSERT, UPDATE
as
begin
Update tblWinMasterDueList set mst.Manager= ist.Manager
FROM tblWinMasterDueList mst INNER JOIN @Insert ist ON mst.jobCostCenter = ist.home_jobcost_nbr
end
September 21, 2006 at 4:02 pm
Daryl, you overcomplicated the statement and made couple of mistakes.
CREATE trigger UpdateWinMasterDueList on dbo.[Manager_List]
for INSERT, UPDATE
as
begin
Update tblWinMasterDueList
SET Manager= i.Manager
FROM inserted i
WHERE tblWinMasterDueList.jobCostCenter = i.home_jobcost_nbr
end
And 2 Frances:
Don't use variables in triggers!
NEVER!!!
_____________
Code for TallyGenerator
September 22, 2006 at 5:04 am
And 2 Frances:
Don't use variables in triggers!
NEVER!!!
Why ? Thanks.
September 22, 2006 at 6:00 am
Trigger does not have parameters.
The only parameters for triggers are tables INSERTED and DELETED.
Only things trigger may reference from outside world are other tables in database.
So, there is no place where variables may come from.
Values in tables are you variables. They are all already declared and sorted out.
Appearance of a variable almost everytime is a sign of cursoring - explicit or hidden. Or, even worse, processing only one row from INSERTED or DELETED and ignoring other ones.
And cursoring inside of trigger is something much worse that cursoring inside of SP.
Trigger is a part of INSERT/DELETE/UPDATE transaction and it applies locks to all affected tables.
Any error during trigger execution suppose to rollback all changes, that's why there is no way to avoid those locks.
That's why trigger must be carefully designed to do the job in the most effective way.
And most effective way does not involve replacing tables with variables or row by row processing.
_____________
Code for TallyGenerator
September 22, 2006 at 8:38 am
how about this one, I tried to update the another table while Quiz Log update.
so I can have current quiz data from the report. Thanks. so many join.
CREATE TRIGGER [Update Quiz Date] ON [dbo].[Quiz Log]
FOR INSERT, UPDATE
--insert the employee info into the tblWinMasterDueList
AS
insert into tblWinMasterDueList(JobCostCenter,JobCostCenterName, WinCourseName, CDate,FullName, Unit, UnitDescription)
SELECT HREMP_adp.CC AS CCNO, HREMP_adp.CCNAME AS CCName, i.[Quiz Name] AS QuizName,
i.[Access Quiz Time] AS TDate, [FULL NAME] as FullName, Entity.Code ,Entity.Description
FROM inserted i INNER JOIN
HREMP_adp ON i.[Employee Name] = HREMP_adp.[FULL NAME] AND i.DOB = HREMP_adp.DOB INNER JOIN
Entity ON HREMP_adp.ENTITY = Entity.Code INNER JOIN
Manager_List ON HREMP_adp.CC = Manager_List.HOME_JOBCOST_NBR
September 22, 2006 at 7:52 pm
Actually this query is quite OK.
But you say you want an UPDATE and performing INSERT.
And your INSERT potentially creates duplications. It's not good.
It must be something like that:
UPDATE L
SET JobCostCenter = H.CC AS CCNO,
JobCostCenterName = H.CCNAME AS CCName,
CDate = i.[Access Quiz Time],
Unit = E.Code,
UnitDescription = E.Description
FROM inserted i
INNER JOIN HREMP_adp H ON i.[Employee Name] = H.[FULL NAME] AND i.DOB = H.DOB
INNER JOIN Entity E ON H.ENTITY = E.Code
INNER JOIN Manager_List M ON H.CC = M.HOME_JOBCOST_NBR
INNER JOIN tblWinMasterDueList L ON L.FullName = H.[FULL NAME] and L.WinCourseName = i.[Quiz Name])
insert into tblWinMasterDueList
(JobCostCenter,JobCostCenterName, WinCourseName, CDate,FullName, Unit, UnitDescription)
SELECT H.CC AS CCNO, H.CCNAME AS CCName, i.[Quiz Name] AS QuizName, i.[Access Quiz Time] AS TDate, H.[FULL NAME] as FullName, E.Code, E.Description
FROM inserted i
INNER JOIN HREMP_adp H ON i.[Employee Name] = H.[FULL NAME] AND i.DOB = H.DOB
INNER JOIN Entity E ON H.ENTITY = E.Code
INNER JOIN Manager_List M ON H.CC = M.HOME_JOBCOST_NBR
WHERE NOT EXISTS (select 1 from tblWinMasterDueList L
where L.FullName = H.[FULL NAME] and L.WinCourseName = i.[Quiz Name])
Not sure what tou are updating, so probably my logic is not right. Check out which fields must by criteria for matching and which ones - subject to update.
And of course table tblWinMasterDueList MUST NOT contain such fields as JobCostCenter,JobCostCenterName, WinCourseName, lName, Unit, UnitDescription. It must have CC_ID, WinCourseID, EmployeeID, UnitID.
When you need correcponding names you retrieve it using JOIN.
Regarding "too many joins". Too many joins is when you cannot see entire FROM statement on 19" screen.
And numerous joins don't mean bad performance.
My colleagues were shocked recently when I improved performance of problematic query just by adding 2 more INNER JOINs and shifting search by the same ID from main to lookup tables.
It was
WHERE H.AID = @AID
I made it
INNER JOIN TableA A ON A.ID = H.AID
WHERE A.ID = @AID
Execution time dropped from 15 seconds to less than 1 second.
So, use joins as much as you can. Always try to reference lookup tables.
And make sure you don't have the same Entity Name mentioned twice in your database. Reference entities only by IDs.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply