mysql - SQL Trigger, Update view after insert

Question

I'm working on a database for a school project. I want to create a trigger, that updates a view and sorts it ascending, after data has been inserted into the view.

Thats what I have at the moment. I would be thankful if you guys could help me, thank you!

 AFTER INSERT
 AS
 UPDATE vw_Highscore

question from:https://stackoverflow.com/questions/65926470/sql-trigger-update-view-after-insert

Answer 1

I assume you mean to sort by one specific column in ascending order on the view table. Well that's easy. You definitely don't need a trigger for that. Simply include the Primary key, or any index in your view table.

When you have a primary key or any type of index placed on your tables, they will automatically be stored in ascending order because of how BTREE indexes work. So when you query them they will always be sorted unless another index overrides them. Primary key overrides all other indexes in most cases. However, if you want to include a primary key in the query, but sort by something else, then use the ORDER BY clause.

For example, say I have a customer table with ID, name and address column. When I do SELECT * FROM customer; I would get this result:

ID    name
1     John
2     Adam
3     Sue

Notice how the id is sorted and the name column is not?

If I then change my query to be SELECT * FROM customer ORDER BY name; Now I will get this result:

ID    name
2     Adam
1     John
3     Sue

Since your view table is synced with the base tables this means you should always have up-to-date sorted results on your view.

Alternatively, if you want to hard code it into your view table without relying on indexes at all, then just use this syntax for your create view statement: CREATE VIEW view_name AS SELECT * FROM base_table ORDER BY column.

Now your view will always be storing its results in sorted format by the column you specify in the ORDER BY section.

Besides the solutions I mentioned above, you could also try and make a CREATE TRIGGER statement. But based on your question as is, that seems like a over-complicated and pointless way to do it. Updating a view is the same as updating it's base table, so if an insert statement is taking place, then that is already going to update your view table automatically. And if like I mentioned above, you have a index or primary key from the base table included in your view or you used ORDER BY clause in your CREATE VIEW statement, then it will sort itself alphabetically/numerically in Ascending order without you needing to take additional steps.