Delete Trigger

I'm trying to get a record added to clsrecord_accessed
when deleting (as an example) notes from the LONGNOTES
field. This isn't appearing to happen though with the
below code. What am I missing?
CREATE TRIGGER Prescott_Delete_ACTIVITY
ON sysdba.ACTIVITY
FOR DELETE
AS
IF UPDATE (LONGNOTES)
or UPDATE (DESCRIPTION)
or UPDATE (STARTDATE)
BEGIN
IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
= 'ABENJ0000022')
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Delete' FROM
Deleted D WHERE AccountID = 'ABENJ0000022'
ENDCheck out BOL for "create trigger". IF UPDATE() is not useful in a delete
trigger since columns are "mentioned" in the delete statement.
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||> columns are "mentioned" in the delete statement.
Did you mean "not mentioned"?|||so I change it to this, and still nothing:
CREATE TRIGGER Prescott_Update_ACTIVITY
ON sysdba.ACTIVITY
FOR Insert, Update, Delete
AS
IF NOT EXISTS (SELECT * FROM Inserted I WHERE I.AccountID
= 'ABENJ0000022'
IF EXISTS (SELECT * FROM Deleted I WHERE I.AccountID
= 'ABENJ0000022'
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Inserted I on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change' FROM
Inserted I
ELSE
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted' FROM
Deleted D
GO
>--Original Message--
>Check out BOL for "create trigger". IF UPDATE() is not
useful in a delete
>trigger since columns are "mentioned" in the delete
statement.
>"CalTab" <anonymous@.discussions.microsoft.com> wrote in
message
>news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>> = 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>> END
>
>.
>|||CalTab (anonymous@.discussions.microsoft.com) writes:
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>= 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
I took the liberty to reformat your trigger body, to make it more
readable:
1) IF NOT EXISTS (SELECT * FROM Inserted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
2) IF EXISTS (SELECT * FROM Deleted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
3) IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Inserted I
on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
BEGIN
A) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, REATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change'
FROM Inserted I
END
ELSE IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Deleted D
on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
BEGIN
B) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, CREATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted'
FROM Deleted D
END
END
END
So if this code is trigger by a DELETE, we will pass the first IF
statement, because Inserted is empty in a DELETE trigger. If the account
is the right one, we will pass the second IF statement too. And we
will always pass the third IF statement, because Inserted is empty.
This means that we will execute INSERT statement A, but since Inserted
is empty, nothing will happen.
The test in the ELSE IF statement is never executed, and neither is
INSERT statement B, when trigger is fired by DELETE.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uh... why do you have hard-coded conditionals in your trigger? Wouldn't it
make more sense to put these values somewhere instead of hard-coding values?
Why are you using the inserted table (it is not populated by a DELETE)?
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||so, is this code useable?
>--Original Message--
>CalTab (anonymous@.discussions.microsoft.com) writes:
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>>= 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA
JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>I took the liberty to reformat your trigger body, to
make it more
>readable:
>1) IF NOT EXISTS (SELECT * FROM Inserted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>2) IF EXISTS (SELECT * FROM Deleted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>3) IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED CA
> JOIN Inserted I
> on CA.CLSRECORD_ACCESSEDID =I.ACTIVITYID)
> BEGIN
>A) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, REATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE
(), I.CONTACTID,
> I.ACCOUNTID, 'Activity Change'
> FROM Inserted I
> END
> ELSE IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED
CA
> JOIN Deleted D
> on
CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> BEGIN
>B) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, CREATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE
(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Deleted'
> FROM Deleted D
> END
> END
> END
>
>So if this code is trigger by a DELETE, we will pass the
first IF
>statement, because Inserted is empty in a DELETE
trigger. If the account
>is the right one, we will pass the second IF statement
too. And we
>will always pass the third IF statement, because
Inserted is empty.
>This means that we will execute INSERT statement A, but
since Inserted
>is empty, nothing will happen.
>The test in the ELSE IF statement is never executed, and
neither is
>INSERT statement B, when trigger is fired by DELETE.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/boo
ks.asp
>.
>|||AnonymousWheel (anonymous@.discussions.microsoft.com) writes:
> so, is this code useable?
I don't if you are the person who asked the original question, or someone
else.
Obviously, the trigger code in its current form does not seem to be
extremely useful, since it does not perform the intended task.
But I can't tell how from the mark it is, since I don't know the
business requirements.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Oops - yes, exactly.
"Foo Man Chew" <foo@.man.chew> wrote in message
news:uFsfwZX0DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > columns are "mentioned" in the delete statement.
> Did you mean "not mentioned"?
>|||all we're trying to do is whenever there is an
Insert,update or delete in the activity table, we'd like a
row inserted in clsrecord_accessed to produce an "audit"
of the activity.
>--Original Message--
>AnonymousWheel (anonymous@.discussions.microsoft.com)
writes:
>> so, is this code useable?
>I don't if you are the person who asked the original
question, or someone
>else.
>Obviously, the trigger code in its current form does not
seem to be
>extremely useful, since it does not perform the intended
task.
>But I can't tell how from the mark it is, since I don't
know the
>business requirements.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>