September 4, 2013 at 1:50 pm
Hello trying to create this code:
CREATE TRIGGER [dbo].[Check_For_Term] ON [dbo].[HRIS_Employee_Archive]
FOR INSERT
AS
Insert Into HRIS_Term_Employee(
Last_Name,
First_Name ,
M_Name ,
Emp_ID ,
Business_Unit ,
Job_Title ,
Supervisor_ID,
Supervisor_Last_Name ,
Supervisor_First_Name ,
Floor,
DT)
Select ne.Last_Name, ne.First_Name, ne.M_Name,
ne.Emp_ID,
ne.Home_Bu+' - '+fin.BUNAME as 'Business Unit',
j.Job_Title,
ne.Supervisor_ID,
case When nes.Last_Name is null then 'N/A' else nes.Last_Name end AS 'S_Last_Name',
case When nes.First_Name is null then 'N/A' else nes.First_Name end AS 'S_First_Name',
ne.Floor,
ne.DT
from DBO.HRIS_Employee_Archive ne,dbo.HRIS_JobTitle j,CRS_Financial.dbo.BUDEPT fin,dbo.HRIS_Employee nes
Where ne.Job_ID=j.Job_ID
AND
ne.Home_Bu=fin.BU
and
ne.Supervisor_ID*=nes.Emp_ID
AND ne.Log_Type='New'
AND ne.DT is not Null
AND ne.Emp_ID in (Select Emp_ID from DBO.HRIS_Employee_Archive Where Log_Type='OLD' AND DT Is Null)
AND ne.EMP_ID not in (select Emp_ID from HRIS_Term_Employee)
GO
CREATE TRIGGER [dbo].[Delete_Non_Term] ON [dbo].[HRIS_Employee_Archive]
FOR INSERT
AS
create table #Temp (EMP_ID int)
Insert into #Temp (EMP_ID)
Select ne.EMP_ID
from DBO.HRIS_Employee_Archive ne,dbo.HRIS_JobTitle j,CRS_Financial.dbo.BUDEPT fin,dbo.HRIS_Employee nes
Where ne.Job_ID=j.Job_ID
AND
ne.Home_Bu=fin.BU
and
ne.Supervisor_ID*=nes.Emp_ID
AND ne.Log_Type='OLD'
AND ne.DT is not Null
AND ne.Emp_ID in (Select Emp_ID from DBO.HRIS_Employee_Archive Where Log_Type='NEW' AND DT Is Null)
AND ne.EMP_ID in (select Emp_ID from HRIS_Term_Employee)
delete from HRIS_Term_Employee
where HRIS_Term_Employee.EMP_ID in (Select EMP_ID from #TEMP)
GO
I get this error:
Msg 4147, Level 15, State 1, Line 17
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'AND'.
Msg 4147, Level 15, State 1, Procedure Delete_Non_Term, Line 17
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
Msg 156, Level 15, State 1, Procedure Delete_Non_Term, Line 20
Incorrect syntax near the keyword 'AND'.
September 4, 2013 at 2:22 pm
The syntax errors are because this is using REALLY old sql syntax. IIRC *= means a left join.
You really need to convert this to ANSI-92 style joins instead of the older style join and get rid of the even older left join style.
Unfortunately this is not the end of your issues here. You have created this as a trigger when inserting into a table. Nowhere in your code do you reference the inserted virtual table. This is generally the point of a trigger, so you can capture the data that is being inserted.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply