Instead of Trigger with Insert and Update

[Steffi1013]

Is it possible to have an "Instead Of" trigger that will do and insert and update in the same trigger?

For example if the record is new I want the "instead of insert" to work and if the record already exists, I want the "instead of update" to kick off

 

[SQLSister]

Instead of replaces the action that actually happened. If it was an inseert that was sent, the instead of insert trigger will kick off, id it was an update that was sent, the instead of update will kick off.

Normally the actions would be different depending on which of these happened orginally which is why they are separate kinds of triggers. Further the original insert or update will NOT happen in an instead of trigger. If you want an insert or update to happen in the trigger, you must specify it inthe instead of trigger. Instead of triggers can be quite tricky and should be avoided if possible. Is it possible that what you really need is a normal trigger which does the action and then some other action happens in additon to the insert or update?

Create two triggers if you need instead of functionality, one with the actions is an update was sent and one with the actions if an insert was sent.

"NOTHING is more important in a database than integrity." ESquared

 

[Steffi1013]

I am working with Credit Card numbers and I am looking for my Instead Of trigger to "Instead of" inserting in the original CCNum field and CCExp field, to do an insert in the Encrypted Fields called CCNum_enc and CCExp_enc and make the CCNum field and CCExp field NULL.

Also let's say someone changes their expiration date on their CC, I want the trigger to update that field. The original fields will stay null but the encrypted fields will update with the new expiration date.

This is the code of what I am trying to do...

ALTER trigger [dbo].[TR_m_payments_ENC]
on [dbo].[m_payments_ENC] instead of INSERT, update
as
BEGIN

SET NOCOUNT ON;

Open symmetric key sk_employees decryption by certificate cert_sk_admin

insert into [m_payments_ENC]
(
CCNum, CCExp, CCNum_en, CCExp_en, CCNum_l4, OtherStuff--, CCNum_IDX
)
select
NULL,
NULL,
encryptbykey( key_guid('sk_employees'), CCNum, 1, HashBytes('SHA1', CONVERT( varbinary, payID))),
encryptbykey( key_guid('sk_employees'), CCExp, 1, HashBytes('SHA1', CONVERT( varbinary, payID))),
RIGHT([inserted].[CCNum], 4),
[inserted].OtherStuff
from inserted


UPDATE [m_payments_ENC]
set CCNum = NULL,
CCExp = NULL,
CCNum_en= encryptbykey( key_guid('sk_employees'), [inserted].CCNum, 1, HashBytes('SHA1', CONVERT( varbinary, [m_payments_ENC].payID))),
CCExp_en= encryptbykey( key_guid('sk_employees'), [inserted].CCExp, 1, HashBytes('SHA1', CONVERT( varbinary,[m_payments_ENC].payID))),
CCNum_l4 = RIGHT([inserted].[CCNum], 4),
OtherStuff = [inserted].OtherStuff,
from [m_payments_ENC], inserted
WHERE [m_payments_ENC].payID = inserted.payID
and inserted.CCExp is not null


CLOSE SYMMETRIC KEY sk_employees
end