How to create this kind of Trigger?

Question

Post reply

How to create this kind of Trigger?

Agile

Ten Centuries

Points: 1153
More actions
September 2, 2009 at 9:21 pm

#73318

I'm planning to make a trigger with this condition
CREATE TRIGGER [TRIGDelete] ON [dbo].[tempEmp]
FOR DELETE
AS
INSERT INTO TextData(TextData) VALUES ()
Where the values is from another table, for example I will create trigger in Table A so everytime there's a deletion of row in Table A, Alll data that was deleted will be saved in Table B.
Im hoping someone can help me...
Thank you in advance...
Thanks,
Morris

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Try something along the lines of....

CREATE TRIGGER [TRIGDelete] ON [dbo].[tempEmp]

FOR DELETE

AS

INSERT INTO TextData(Column1, Column2, ...) SELECT Column1, Column2, ... FROM Inserted

You may also want to consider whether you wish to store additional information such as the date/time that the deletion occurred and who did it

Sergiy SSC Guru Points: 110208 More actions · Answer 2

CREATE TRIGGER [TRIGDelete] ON [dbo].[TableA]

FOR DELETE

AS

INSERT INTO TableB (Column List)

SELECT Column List

FROM deleted

GO

_____________
Code for TallyGenerator

Agile Ten Centuries Points: 1153 More actions · Answer 3

Thank you for the reply..

CREATE TRIGGER [TRIGDelete] ON [dbo].[TableA]

FOR DELETE

AS

INSERT INTO TableB (Column List)

SELECT Column List

FROM deleted

GO

On this trigger, How will I know of what data was deleted? For ex. I delete row 6, so the data from row6 is the one I will save to TableB.

Thanks,
Morris

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Please read the manual, BOL in this case.

Topic CREATE TRIGGER.

Table deleted contains only deleted records.

_____________
Code for TallyGenerator

Agile Ten Centuries Points: 1153 More actions · Answer 5

Thank you for the responce and sorry I did not clear these things. This is what I want in my Trigger.

CREATE TRIGGER [TRIGDelete] ON [dbo].[TableA]

FOR DELETE

AS

INSERT INTO TableB (Column List)

SELECT Column List

FROM TableA

GO

There's a possibility that there will be a deletion of data on TableA that's why I plan to create a Trigger for delete, and saved the deleted row from TableA to TableB. My problem is that how will I know of what row is deleted in TableA.

I need to know the data that was deleted in TableA so I can Insert that data to TableB. Im stock with this trigger code. I really need help to do this...

Thanks,
Morris

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Once again:

ALL DELETED ROWS ARE IN THE TABLE "DELETED".

Just use my code as it is.

_____________
Code for TallyGenerator

Agile Ten Centuries Points: 1153 More actions · Answer 7

Alright I will explain more of my issue on this task.

First Im not allowed to modify the software that can do deletion of data in our SQL server. So when that software delete a row on that table, it's totally deleted. There is no other tables for deleted data. That's why I comeup to a solution to put a trigger so Im the one who will create a table for deleted data. So everytime that software did a deletion of data my trigger will Insert the data that was deleted to the table I created "TableB"

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 8

Damien (9/3/2009)
Alright I will explain more of my issue on this task.
First Im not allowed to modify the software that can do deletion of data in our SQL server. So when that software delete a row on that table, it's totally deleted. There is no other tables for deleted data. That's why I comeup to a solution to put a trigger so Im the one who will create a table for deleted data. So everytime that software did a deletion of data my trigger will Insert the data that was deleted to the table I created "TableB"

When a row is deleted from a table, a "Delete" trigger on the table will have the rows currently being deleted stored in a special trigger table literally called "Deleted". ONLY the rows being deleted will actually be in the table called "DELETED" within the trigger. It's not a table that you make, it's a table that SQL Server makes for use within triggers as a trigger fires. Like Sergiy is trying to explain... it's just there and it will contain only but all of the rows just deleted from Table A.

Sergiy is correct on two other things... first, the code example he gave for your problem is spot on. Second, I agree... for a deeper understanding of how triggers and their related INSERTED and DELETED tables work, you should probably curl up with Books Online for an hour or two and deeply study the CREATE TRIGGER section.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Agile Ten Centuries Points: 1153 More actions · Answer 9

THANK YOU VERY MUCH TO YOU ALL!!

Im sorry I did not understand the first one that was suggested to me. I'm starting to read online books regarding trigger. Again im sorry for misunderstanding and thank you.

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 10

Damien (9/3/2009)
THANK YOU VERY MUCH TO YOU ALL!!
Im sorry I did not understand the first one that was suggested to me. I'm starting to read online books regarding trigger. Again im sorry for misunderstanding and thank you.

Heh... no problem. You got it and you're doing the absolute right thing now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon Liddle SSCrazy Points: 2580 More actions · Answer 11

Damien (9/3/2009)
I'm starting to read online books regarding trigger.

Just to clarify, when people recommend you read Books Online (often shortened to BOL), they usually refer to the documentation of the same name which is installed when you install the SQL Server client tools (I think) - from Query Analyzer, use Shift+F1, or find it in your Start menu under Microsoft SQL Server.

I don't know how many people starting with SQL Server realise it is there but you should become very familiar with it and get into the habit of referring to it to find out more about features of SQL Server.

Of course, referring to other resources is a good idea, but BOL is a great place to start imho.

Hope this helps!

Simon 🙂