Trigger on delete - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger on delete

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-03 : 10:07:20

I have never written a trigger and need a little help.

I have some people doing stupid record deletes in our pallet Inventories.

I need to make a Trigger that when an entire record is deleted (not just part of the data) that it would insert the data from that record into another table.

I know the logic but need some help on the syntax.

CREATE TRIGGER PalletCount_Sub_Trigger1
ON dbo.PalletCount_Sub
FOR DELETE
AS
If Delete (entire Record)
Begin

INSERT INTO dbo.PalletCount_deleated
(Pallet_ID, Job_Number_Sub_Link, Pallet_Date, Case_Count, Pallet_Number, Inv_Updated, Pallet_Item_Count, Pallet_Day, Partial_Case, Printed,
Pal_Type)
SELECT Pallet_ID, Job_Number_Sub_Link, Pallet_Date, Case_Count, Pallet_Number, Inv_Updated, Pallet_Item_Count, Pallet_Day, Partial_Case, Printed,
Pal_Type
FROM Dleleted dbo.PalletCount_Sub

End

I also Need to update a field Called whoDeleted with the NT Username (I use NT Validation to sign on to SQL)

Any help would leave me eternally grateful as this is a moron hunt.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 10:24:57

Jim,

The following tracks Deletes as well as updates. The changes get written to an (almost) identical history table...



CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE 
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
	BEGIN

		Insert Into Company_H (
		       HIST_ADD_TYPE
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts
		)
		Select
		       'U'
		      ,(Select Inserted.Updated_By from Inserted 
			Where Deleted.Company_Name	= Inserted.Company_Name)
		      ,user
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts	  
		From  Deleted	   
	END


If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

	BEGIN

		Insert Into Company_H (
		       HIST_ADD_TYPE
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts
		)
		Select
		       'D'
		      ,user
		      ,user
		      ,Company_Name
		      ,Active_Ind
		      ,Psft_Company_Id
		      ,FEIN
		      ,Has_NonQual_Taxes
		      ,Has_Qual_Taxes
		      ,Created_By
		      ,Created_Ts
		      ,Updated_By
		      ,Updated_Ts	  
		From  Deleted	
	END

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-03 : 10:36:54

Ok So far I got this

CREATE TRIGGER PalletCount_Sub_Trigger1
ON dbo.PalletCount_Sub
FOR DELETE
AS
If exists (Select 1 From Deleted)
Begin

INSERT INTO dbo.PalletCount_deleated
(Pallet_ID, Job_Number_Sub_Link, Pallet_Date, Case_Count, Pallet_Number, Inv_Updated, Pallet_Item_Count, Pallet_Day, Partial_Case, Printed,
Pal_Type)
SELECT Pallet_ID, Job_Number_Sub_Link, Pallet_Date, Case_Count, Pallet_Number, Inv_Updated, Pallet_Item_Count, Pallet_Day, Partial_Case, Printed,
Pal_Type
FROM Deleted

End

Now all I need to get is the NT Username to put in my WhoDeleted Field.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 10:45:53

You don't need the IF EXISTS (SELECT 1 FROM DELETED) if the trigger is only for DELETE

I had to do that because the trigger is for both UPDATE and DELETE...

And anyway, just checking if the row is in deleted doesn't mean that it couldn't be an update...

an update will have a row in inserted as well as deleted...

Also you got a type-o in the SELECT FROM clause

And User is the Id of who's connected, like in my example...

looks good otherwise...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-03 : 10:55:36

Thanks Works Great Execpt "User" only returns the SQL Username not The NT username.

Dang I had that Syntax And threw it out.

We Need a Kicking oneself Icon.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 10:57:45

What security method are you using?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-03 : 11:00:53

Windows NT Intigrated Security.

Jim
Users <> Logic

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-10-03 : 11:01:17

Jim

I think what your looking for is User = SUSER_sname(SUSER_SID())

OMB

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-03 : 11:05:08

Yep thats it!!!!!!!!!!!!! You guys made my day.

Thanks Much.

Now to hunt down one deleting dumb @$$

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 11:25:27

NO SH_T....

Damn, got some triggers to change...

THANKS!

Damn, learned something new again...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources