Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Showing posts with label tblsalarymain. Show all posts
Showing posts with label tblsalarymain. Show all posts
Wednesday, March 21, 2012
Insert Trigger
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Try:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Try:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Insert Trigger
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Subscribe to:
Posts (Atom)