December 3, 2012 at 7:10 pm
Hi ,
I am trying to create the below trigger.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- =============================================
CREATE TRIGGER [dbo].[TestUpdate]
ON [dbo].[Students]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
declare @Group Table
(
[Group] Varchar(200) NULL,
[FirstName] Varchar(200) NULL,
[LastName] Varchar(200) NULL,
StudentID int ,
[LowerRange] int null,
[UpperRange] int null,
[CCC] [nvarchar](20) NULL
);
INSERT INTO @Group
select Name as [Group],
FirstName,
LastName,
StudentID,
LowerRange,
UpperRange,
ISNULL(CCC,'') AS CCC
from [TestDB].[dbo].[Students] as a,
[TestDB].[dbo].[StudentRanges] as b
where a.StudentID>=b.LowerRange
and a.StudentID<=b.UpperRange ;
declare @UserName Table
(
StudentID int ,
[UserName] [nvarchar](100) NULL
);
INSERT INTO @UserName
SELECT
a.StudentID,
CASE
WHEN a.StudentID < 30000 THEN ISNULL(a.[FirstName], '') + ISNULL(a.[Initial], '') + ISNULL(a.[LastName], '') + '_' + CONVERT(VARCHAR, a.[StudentID])
ELSE ISNULL(a.[FirstName], '') + ISNULL(a.[Initial], '') + ISNULL(a.[LastName], '') + '_' + b.[Group]
END as UserName
FROM [TestDB].[dbo].[Students] a
JOIN @Group b ON a.StudentID = b.StudentID
ORDER BY a.StudentID ;
-- Is Row Being Deleted
IF (SELECT COUNT(*)
FROM [TestDB].[dbo].[Students] a
JOIN @Group b ON a.StudentID = b.StudentID
JOIN DELETED c ON b.StudentID = c.StudentID
) > 0
BEGIN
IF (SELECT COUNT(*)
FROM [TestDB].[dbo].[Students] a
JOIN @Group b ON a.StudentID = b.StudentID
JOIN DELETED c ON b.StudentID = c.StudentID
WHERE a.[StudentUID] IS NULL
) > 0
BEGIN
-- StudentID is Null. So trigger is going to end.
SELECT 'Trigger Named TestUpdate ended'
--GOTO Branch_One ;
END ;
ELSE
BEGIN
-- Remove Badge
INSERT INTO [TestingDB].[dbo].[tbOperationIn]
([PkIn]
,[StudentNumber]
,[StudentOperation]
,[StudentInfo1]
)
SELECT
ROW_NUMBER() OVER(ORDER BY a.[StudentID]) AS Pkln
,SUBSTRING(a.[StudentUID], 1, 4) + ':' + SUBSTRING(a.[StudentUID], 5, 4)as StudentNumber
,1 as StudentOperation
,a.[StudentID] as StudentInfo1
FROM [TestDB].[dbo].[Students] a
JOIN @Group b ON a.StudentID = b.StudentID
JOIN DELETED c ON b.StudentID = c.StudentID;
END ;
END ;
ELSE
-- Row is Not Deleted
-- Checking if StudentID is Null
IF (SELECT COUNT(*)
FROM [TestDB].[dbo].[Students] a
JOIN @Group b ON a.StudentID = b.StudentID
JOIN INSERTED c ON b.StudentID = c.StudentID
WHERE a.[StudentUID] IS NULL
) > 0
BEGIN
-- StudentID is Null. So trigger is going to end.
SELECT 'Trigger Named TestUpdate ended'
-- GOTO Branch_One ;
END ;
ELSE
--Checking for StudentID is Modified, Status = 'A', Expired is null or If it is going to expire in the future starting from today.
BEGIN
--Checking for StudentID is Modified.
IF UPDATE (StudentUID)
BEGIN
--Checking for StudentID is Modified, Status = 'A', Expired is null or If it is going to expire in the future starting from today.
IF ( SELECT COUNT(*)
FROM [TestDB].[dbo].[Students] a
JOIN INSERTED b ON a.StudentID = b.StudentID
WHERE a.[Status] = 'A'
AND a.[Expires] IS NULL
OR a.[Expires] >= GETDATE()) > 0
BEGIN
-- ADD Badge
IF (select count(*)
FROM [TestingDB].[dbo].[tbStudent] a
JOIN @Group b ON a.[StudentInfo1] = b.StudentID
JOIN INSERTED c ON b.StudentID = c.StudentID
)> 0
BEGIN
INSERT INTO [TestingDB].[dbo].[tbOperationIn]
( [PkIn]
,[StudentNumber]
,[RenameStudentNumber]
,[StudentOperation]
,[FkStudentAccessGroup]
,[StudentInfo1]
,[StudentInfo2]
,[StudentInfo3]
,[StudentInfo4]
,[StudentInfo5]
,[StudentInfo6])
SELECT
ROW_NUMBER() OVER(ORDER BY d.[StudentID]) AS Pkln
,c.[StudentNumber]
,SUBSTRING(d.[StudentUID], 1, 4) + ':' + SUBSTRING(d.[StudentUID], 5, 4)as StudentUID
,3
,CASE
WHEN d.StudentID < 30000 THEN 11473
WHEN d.StudentID >= 30000 THEN
CASE
WHEN a.[JHTAccess] = 0 THEN 559
WHEN a.[JHTAccess] = 2 THEN 11473
WHEN d.[JHTAccess] = 1 THEN 11473
ELSE 559
END
END AS AccessLevel
,d.StudentID
,g.[Div]
,f.[Dept]
,f.[DeptID]
,f.[JobTitle]
,f.[JobCode]
FROM [TestDB].[dbo].[StudentRanges] a
JOIN @Group b ON a.Name = b.[Group]
JOIN [TestingDB].[dbo].[tbStudent] c ON b.StudentID = c.[StudentInfo1]
JOIN [TestDB].[dbo].[Students] d ON c.[StudentInfo1] = d.StudentID
JOIN INSERTED e ON d.StudentID = e.StudentID
JOIN [TestDB].[dbo].[EmployeeDeptJob] f ON e.StudentID = f.StudentID
JOIN [TestDB].[dbo].[DivDeptJob] g ON f.[DeptID] = g.[DeptID]
ORDER BY d.[StudentID]
END ;
ELSE
BEGIN
INSERT INTO [TestingDB].[dbo].[tbOperationIn]
( [PkIn]
,[StudentNumber]
,[UserName]
,[StudentOperation]
,[FkStudentAccessGroup]
,[StudentInfo1]
,[StudentInfo2]
,[StudentInfo3]
,[StudentInfo4]
,[StudentInfo5]
,[StudentInfo6])
SELECT
ROW_NUMBER() OVER(ORDER BY e.[StudentID]) AS Pkln
,SUBSTRING(e.[StudentUID], 1, 4) + ':' + SUBSTRING(e.[StudentUID], 5, 4)as StudentUID
,CASE
WHEN e.StudentID < 30000 THEN ISNULL(e.[FirstName], '') + ISNULL(e.[Initial], '') + ISNULL(e.[LastName], '') + '_' + CONVERT(VARCHAR, e.[StudentID])
ELSE ISNULL(e.[FirstName], '') + ISNULL(e.[Initial], '') + ISNULL(e.[LastName], '') + '_' + b.[Group]
END as UserName
,0
,CASE
WHEN e.StudentID < 30000 THEN 11473
WHEN e.StudentID >= 30000 THEN
CASE
WHEN a.[JHTAccess] = 0 THEN 559
WHEN a.[JHTAccess] = 2 THEN 11473
WHEN e.[JHTAccess] = 1 THEN 11473
ELSE 559
END
END AS AccessLevel
,e.StudentID
,h.[Div]
,g.[Dept]
,g.[DeptID]
,g.[JobTitle]
,g.[JobCode]
FROM [TestDB].[dbo].[StudentRanges] a
JOIN @Group b ON a.Name = b.[Group]
JOIN [TestingDB].[dbo].[tbStudent] c ON b.StudentID = c.[StudentInfo1]
JOIN @UserName d ON c.[StudentInfo1] = d.StudentID
JOIN [TestDB].[dbo].[Students] e ON d.StudentID = e.StudentID
JOIN INSERTED f ON e.StudentID = f.StudentID
JOIN [TestDB].[dbo].[EmployeeDeptJob] g ON f.StudentID = g.StudentID
JOIN [TestDB].[dbo].[DivDeptJob] h ON g.[DeptID] = h.[DeptID]
ORDER BY e.[StudentID]
END ;
END ;
ELSE
BEGIN
--StudentID is Not Modified, Status is not equal to 'A', Expired is not null or If it is going to expire on any day before today.
SELECT 'Trigger Named TestUpdate ended'
--GOTO Branch_One ;
END ;
END ;
ELSE
BEGIN
-- StudentUID is not Modified
IF UPDATE (Status)
BEGIN
-- Check if status is equal to 'A'
IF (SELECT COUNT(*)
FROM [TestDB].[dbo].[Students] a
JOIN INSERTED b ON a.StudentID = b.StudentID
WHERE a.[Status] = 'A') > 0
BEGIN
-- Add Badge
IF (select count(*)
FROM [TestingDB].[dbo].[tbStudent] a
JOIN @Group b ON a.[StudentInfo1] = b.StudentID
JOIN INSERTED c ON b.StudentID = c.StudentID
)> 0
BEGIN
INSERT INTO [TestingDB].[dbo].[tbOperationIn]
([PkIn]
,[StudentNumber]
,[RenameStudentNumber]
,[StudentOperation]
,[FkStudentAccessGroup]
,[StudentInfo1]
,[StudentInfo2]
,[StudentInfo3]
,[StudentInfo4]
,[StudentInfo5]
,[StudentInfo6])
SELECT
ROW_NUMBER() OVER(ORDER BY d.[StudentID]) AS Pkln
,c.[StudentNumber]
,SUBSTRING(d.[StudentUID], 1, 4) + ':' + SUBSTRING(d.[StudentUID], 5, 4)as StudentUID
,3
,CASE
WHEN d.StudentID < 30000 THEN 11473
WHEN d.StudentID >= 30000 THEN
CASE
WHEN a.[JHTAccess] = 0 THEN 559
WHEN a.[JHTAccess] = 2 THEN 11473
WHEN d.[JHTAccess] = 1 THEN 11473
ELSE 559
END
END AS AccessLevel
,d.StudentID
,g.[Div]
,f.[Dept]
,f.[DeptID]
,f.[JobTitle]
,f.[JobCode]
FROM [TestDB].[dbo].[StudentRanges] a
JOIN @Group b ON a.Name = b.[Group]
JOIN [TestingDB].[dbo].[tbStudent] c ON b.StudentID = c.[StudentInfo1]
JOIN [TestDB].[dbo].[Students] d ON c.[StudentInfo1] = d.StudentID
JOIN INSERTED e ON d.StudentID = e.StudentID
JOIN [TestDB].[dbo].[EmployeeDeptJob] f ON e.StudentID = f.StudentID
JOIN [TestDB].[dbo].[DivDeptJob] g ON f.[DeptID] = g.[DeptID]
ORDER BY d.[StudentID]
END ;
ELSE
BEGIN
INSERT INTO [TestingDB].[dbo].[tbOperationIn]
([PkIn]
,[StudentNumber]
,[UserName]
,[StudentOperation]
,[FkStudentAccessGroup]
,[StudentInfo1]
,[StudentInfo2]
,[StudentInfo3]
,[StudentInfo4]
,[StudentInfo5]
,[StudentInfo6])
SELECT
ROW_NUMBER() OVER(ORDER BY e.[StudentID]) AS Pkln
,SUBSTRING(e.[StudentUID], 1, 4) + ':' + SUBSTRING(e.[StudentUID], 5, 4)as StudentUID
,CASE
WHEN e.StudentID < 30000 THEN ISNULL(e.[FirstName], '') + ISNULL(e.[Initial], '') + ISNULL(e.[LastName], '') + '_' + CONVERT(VARCHAR, e.[StudentID])
ELSE ISNULL(e.[FirstName], '') + ISNULL(e.[Initial], '') + ISNULL(e.[LastName], '') + '_' + b.[Group]
END as UserName
,0
,CASE
WHEN e.StudentID < 30000 THEN 11473
WHEN e.StudentID >= 30000 THEN
CASE
WHEN a.[JHTAccess] = 0 THEN 559
WHEN a.[JHTAccess] = 2 THEN 11473
WHEN e.[JHTAccess] = 1 THEN 11473
ELSE 559
END
END AS AccessLevel
,e.StudentID
,h.[Div]
,g.[Dept]
,g.[DeptID]
,g.[JobTitle]
,g.[JobCode]
FROM [TestDB].[dbo].[StudentRanges] a
JOIN @Group b ON a.Name = b.[Group]
JOIN [TestingDB].[dbo].[tbStudent] c ON b.StudentID = c.[StudentInfo1]
JOIN @UserName d ON c.[StudentInfo1] = d.StudentID
JOIN [TestDB].[dbo].[Students] e ON d.StudentID = e.StudentID
JOIN INSERTED f ON e.StudentID = f.StudentID
JOIN [TestDB].[dbo].[EmployeeDeptJob] g ON f.StudentID = g.StudentID
JOIN [TestDB].[dbo].[DivDeptJob] h ON g.[DeptID] = h.[DeptID]
ORDER BY e.[StudentID]
END ;
END ;
ELSE
BEGIN
-- Status is not equal to A
-- Remove Badge
INSERT INTO [TestingDB].[dbo].[tbOperationIn]
([PkIn]
,[StudentNumber]
,[StudentOperation]
,[StudentInfo1]
)
SELECT
ROW_NUMBER() OVER(ORDER BY a.[StudentID]) AS Pkln
,SUBSTRING(a.[StudentUID], 1, 4) + ':' + SUBSTRING(a.[StudentUID], 5, 4)as StudentNumber
,1 as StudentOperation
,a.[StudentID] as StudentInfo1
FROM [TestDB].[dbo].[Students] a
JOIN @Group b ON a.StudentID = b.StudentID
JOIN INSERTED c ON b.StudentID = c.StudentID;
END ;
END ;
ELSE
BEGIN
SELECT 'Trigger Named TestUpdate ended' ;
-- GOTO Branch_One ;
END ;
END ;
END ;
GO
--Branch_One:
--SELECT 'Trigger Named TestUpdate ended'
It is giving the below error message.
Msg 102, Level 15, State 1, Procedure TurnstileUpdate, Line 424
Incorrect syntax near 'END'.
can you please fix the tsql syntax.
Thank You,
December 4, 2012 at 7:44 am
I would hate taking over the maintenance of a database which contained triggers like this. On behalf of those who may have to maintain your code in future, please reconsider. Or take some time to explain why you think you need to use a trigger for this and see what advice you receive.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 4, 2012 at 4:14 pm
To fix the syntax error, you need one more END. The BEGIN block for the trigger is not closed.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 5, 2012 at 2:54 pm
Thank You very much Todd.
My Trigger got executed successfully with your help
December 5, 2012 at 3:10 pm
Hi Phil,
Business developers had asked me to create a trigger like that.
I hate to write triggers like that. I will check with the developers.
Thank You,
December 5, 2012 at 5:07 pm
Hi Celko,
I got the business requirement like this. Based on the business requirement I had created the code.
I will follow your recommendations. Thank You very much.
Thank You,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply