Hi TurkHackTeam members.
I'll answer What is Trigger ? Why we use Trigger ? and How to Use Trigger ? questions to you today.
What Is Trigger ?
This trigger process can be occured before or after any process.
In Which Situations Triggers Step In?
"After Trigger" or "For Trigger" step in after stated process
Using
Note; After and Instead Of are making same processes
Try on Examples
I'll try processes on Nortwind as example database. Tables are related. So, i will copy one of them with bulk instert.
Now write a trigger over "YedekCustomers"
We wrote a trigger for rollback. We wrote rollback because we want to block delete processes. Lets try to delete.
As you can see, we got an error message. Our process blocked by trigger.
Note: Delete command blocks by trigger. But If we want to reset all table with Truncate, our trigger wont block that.
We can use that for uptade and insert processes too. You can write update and Instert instead of Delete for change process.
Disable Trigger
For example; we blocked an user's deleting, inserting or updating. I am a database programmer. I need to make a process on this table over Backend. Will i must delete and create a repeatedly? Of course not.
Look, i deleted saved which is have CutomerId. And worked.
If we dont know trigger name, or we need to finish our process quickly, we can make like this;
We will backup deleted datas
We will write trigger
Now we are testing it
As you can see, that process finished succesfully.
I'll answer What is Trigger ? Why we use Trigger ? and How to Use Trigger ? questions to you today.
What Is Trigger ?
This trigger process can be occured before or after any process.
- Transfering of deleted datas from table to other table.
- They realize on virtual tables
- Insterted table for adding
- Deleted table for delete processes
- There is no update table for updates. All update processes are a instert process. So, we are fallowing inserted or deleted table.
- While Trigger is on process (adding or delete processes), can revoke that process for block to data adding or deleting.
In Which Situations Triggers Step In?
"After Trigger" or "For Trigger" step in after stated process
Using
Note; After and Instead Of are making same processes
Try on Examples
I'll try processes on Nortwind as example database. Tables are related. So, i will copy one of them with bulk instert.
Kod:
SELECT * INTO YedekCustomers FROM dbo.Customers c
Now write a trigger over "YedekCustomers"
Kod:
CREATE TRIGGER DelCustomers
ON YedekCustomers
AFTER DELETE
AS
BEGIN
ROLLBACK
END
We wrote a trigger for rollback. We wrote rollback because we want to block delete processes. Lets try to delete.
As you can see, we got an error message. Our process blocked by trigger.
Note: Delete command blocks by trigger. But If we want to reset all table with Truncate, our trigger wont block that.
We can use that for uptade and insert processes too. You can write update and Instert instead of Delete for change process.
Disable Trigger
For example; we blocked an user's deleting, inserting or updating. I am a database programmer. I need to make a process on this table over Backend. Will i must delete and create a repeatedly? Of course not.
Look, i deleted saved which is have CutomerId. And worked.
If we dont know trigger name, or we need to finish our process quickly, we can make like this;
Kod:
DISABLE TRIGGER ALL
DELETE FROM YedekCustomers WHERE dbo.YedekCustomers.CustomerID = 'ALFKI'
ENABLE TRIGGER ALL
We will backup deleted datas
We will write trigger
Now we are testing it
As you can see, that process finished succesfully.
Source: https://www.turkhackteam.org/databases/1916823-mssql-trigger-kullanimi.html
Translator: M3m0ry
Moderatör tarafında düzenlendi: