delete trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

delete trigger

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2008-09-23 : 12:34:23

i wrote a trigger to insert records (date, primary key, fieldname) to tableA after a delete is perform. Where should I put the trigger? in the parent or child table? I deleted a record using the primary key from the child table. But then it inserted the foreign key instead of the primary to tableA. It's so weird.

create TRIGGER trig_delAuthor
ON computersCA
after delete
AS

declare @primaryKey varchar(30)
declare @tableName varchar(50)
declare @pkType varchar(50)

SELECT @primaryKey = B.COLUMN_NAME,
@tableName = a.table_name,
@pkType = c.data_type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
on A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE = 'PRIMARY KEY'
and a.table_name = 'computersCA'
join INFORMATION_SCHEMA.columns c
on b.column_name = c.column_name

insert Maintenance.DeletedRecord (DeletedRecordID, TableName, PKFieldName, PKType)
select computerNVID,
@tableName,
@primaryKey,
upper(@pkType)
from deleted
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 12:41:36

Which tables delete action reuires you to insert records? put trigger on that table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources